Change measures and switch MTD/YTD using slicers in Power BI || Zebra BI Knowledge Base
VloĹžit
- Äas pĹidĂĄn 30. 07. 2024
- Learn to dynamically switch time periods such as MTD/YTD and change measures. There's a great DAX function for that: SWITCH. Watch the video to see how you can apply it to your reports.
By adding measures to slicers, end-users can interact with your Power BI reports and better understand what is shown.
00:00:00 Intro
00:00:45 Create the SWITCH to change between Periodic and YTD view
00:08:17 Create one dynamic visual
00:24:22 Outro
For more detailed instructions & a PBIX example, please visit:
đ Adding dynamic measures to Power BI reports
bit.ly/3ZH80te
â Subscribe to always get our latest videos: czcams.com/users/ZebraBI?sub_c...
đThis playlist includes:
⢠How To Import & Set-Up Zebra BI Visuals From AppSource
⢠Power BI Income Statement or P&L In Just A Few Steps!
⢠How To Create Powerful Waterfall Charts In Power BI
⢠Loading Data From Data Sources To Power BI
⢠How To Create A Hierarchical Income Statement In Power BI
⢠Add A Custom Calculated Column To Your Power BI Report
⢠Sorting Correctly By Month Name In Power BI
⢠How To Set A Custom Sort Order In Power BI
⢠Power BI Unpivot (From Columns To Rows)
⢠Adding Your Own Columns For Custom Sort In Power BI
⢠How To Create A Calendar Table In Power BI Using Power Query
⢠How To Create A Calendar Table In Power BI Using DAX
⢠Exporting From Power BI To PDF & PowerPoint Slides
⢠Sorting Rows In Zebra BI Tables Visual
... and new video releases every week!
=========================================================
Get more practical tips & PBIX examples:
zebrabi.com/pbi-help/
=========================================================
#PowerBI #PowerBITutorial #ZebraBI
â STAY IN THE KNOWâ
LinkedIn: / zebr. .
Twitter: / zebrabi
Facebook: / zebra.bi - VÄda a technologie
I really loved the way you explained it . very clear. thanks a lot... Nimantha from Sri Lanka
Thank you very much! Such a clear explanation
very clear explanation, thanks
Thanks a LOT Andrej & Team I enjoy your videos and learn about Zebra BI but PowerBI too, thanks
Thank you, Gabriel! It's always a pleasure creating new content for such an amazing community :)
Great video and appreciate you walking us through the steps to set-up & create. One issue I have. I have my PBI slicers for Month and Year set to 7 (Jul) and 2022. I've confirmed my Switch, tied to my MTD/YTD buttons are working correctly. When using the Zebra BI Table visual (v5.4.0) and I click on the MTD button, the visual shows Jul MTD data. However, if I click on the YTD button the visual does not change, it still shows the Jul numbers. Again I've confirmed my SelectedCalc is 2 (YTD). If I remove 7 (Jul) from the PBI slicer then my YTD numbers show. Below is my SWITCH statement, which uses an AGG table. It seems the PBI month slicer for some reason is overriding my YTD switch DATESYTD statement. Thoughts?
Rewards AC Switch = SWITCH([Selected],
1, SUM(AggFactTable[NonSumCount]),
2, CALCULATE(SUM(AggFactTable[NonSumCount]), DATESYTD(DimEarnedDate[Date])))
Summary:
YTD - If my PBI Year Slicer is set to 2022 and Month slicer is ALL, I see my YTD numbers in the visual. All Good
MTD - If my PBI Year Slicer is set to 2022 and Month slicer is set to 7 (Jul), I see my MTD is the visual. All Good However, if I click on the YTD button the visual remains with Jul only data. I have to clear out the PBI Month slicer to see YTD numbers again.
The power and beauty of disconnected tables!!!!! love them as much as your visuals
Awww... that's so sweet đĽ°
And yes, disconnected tables are truly a marvel.
I really loved the way you explained it . Very smooth
Thank you Jatinder!
Very useful and efficient
Thanks A LOT !!!
You're welcome!
Thank you Sir!! Like the visualđ
You're welcome!
Nicely explained. Love zebra BI visuals.
Hi Jitendra, thank you for stopping by! We promise to keep making new videos in the future đ
Thank you so much for this tutorial. Just what I needed. Just curious, is it possible to select more than measure? For example, is there a way to have both revenue and cost showing in the visual?
Hi Reggie, you could select both Revenue and Costs in the drop-down menu, which would result in SUM of Revenues and Cost on the visual. If you would want to display them separately, you would need to structure them a bit differently by having a separate measure for each KPI, or to unpivot KPI columns to the dimension that can then be used as a category or a group.
Very useful feature. Thank you.
I've seen also your other P&L statement examples. I wonder how do you deal with multiple columns for each position in P&L statement and show % share of Turnover for each cost position, so you can compare structural change AC vs PY? I haven't found a case like this yet.
Hi Luka, this is a great question. We explained this in our latest product update video with the new feature in Zebra BI Tables visual 4.3 called âScaled groupsâ:
czcams.com/video/ARYY6M16NRA/video.html In the description of that video there is also a link to download the PBIX demo file so you can check it out and see how it was done. Hope this helps :)
How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?
Thank you
You're welcome!
Hi, we have implemented the aproach you presented here but with a lot of modifications. We have a disconnected tables for Month/YTD/MTD, Currency selection (2 possible), Plan type (3 possible), Forecast type (2 possible), Cost type (2 possible) and KPIs (23 possible). We use nested SWITCHes for calculating AC, PL, PY, FC. Unfortunatelly our model started to work very slow. Do you have any idea how so complicated SWITCHes work? It looks like they evaluate every possible combination even if the result is not going to be used. When we put these 4 scenarios into Zebra table the visual refreshes very slow. At the same time it works fine in Zebra chart (same values, groups, categories in both visuals). Why is this happening?
Hi Arkadiusz, this might be a bit too extensive to be able to solve here. There's a great article for optimizing such SWITCH statements done by Marco Russo of SQLBI that could be useful: www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/. Hope this helps!
Thank you for your great tips. is it possible to have custom 445 calendar measure(sales) which can be shown dynamically? I tried dateadd function but it's doesn't work with 445
Ofcourse you can make it work with 445 calendars, the only thing you need to know to make it shift to prior year correctly is if the previous year had 52 or 53 weeks in the 445 calendar. Based on that you "add" -52 or -53 weeks. So that is -364 days or -371 days.
Hi Mohamed, we suggest that you look into this article on DAX Patterns to learn more about week-related time intelligence: www.daxpatterns.com/week-related-calculations/. Hope this helps!
Awesome
Cool earring
Great tutorial! Thank you very much for sharing this valuable information.
I have tried and itâs working properly!
I have only one question , I donât know how to solve it :
If I set 1 button to chose sales (either QTYâs or Value/Euro) I need to appear currency when I press for Value.
Could be possible to set in the same function âwhole number â for qtyâs and âcurrencyâ for Value ?
Dax formula:
Current Year Sales ( QTYâs vs Value/Euro)=
Switch(
[QTYâs or Value Selection],
1,[Current Year QTYâs],
2,[Current Year Value]
)
Thank you!
I think this could be solved with custom formatting on measure.
If this does not work please reach out to our support with a demo file so we can help you.
When selecting a month let's say Oct. the filter override the monthly or YTD calculation and shows only Oct. am I doing anything wrong?
Did you figure out the solution to your problem? I am currently investigating the same, the month slicer is filtering only for the month rather than the period selected in the slicers.
If I want to set a slicer month to be a
Current month .
Can I do that ? If I can How can I do that ?
Thank youđ
Through above example I have worked for YTD & PYTD . But It reports current YTD but in PYTD no number is coming, through following formula PY = CALCULATE(Sheet1[CY],DATEADD(Sheet1[Date],-1,YEAR)). Kindly help me to solve it.
You have to have a context year from a year slicer (select actual year in a year slicer and should work)