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.
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.
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!
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”: ruclips.net/video/ARYY6M16NRA/видео.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 :)
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.
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!
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.
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.
I really loved the way you explained it . very clear. thanks a lot... Nimantha from Sri Lanka
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 :)
Thank you very much! Such a clear explanation
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.
very clear explanation, thanks
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.
I really loved the way you explained it . Very smooth
Thank you Jatinder!
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.
How have you created sales scenerio measure?
This is the file that was used for the video: zebrabi.com/template/sales-dashboard-power-bi-template/
You can see how everything is made in there.
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!
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”:
ruclips.net/video/ARYY6M16NRA/видео.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 :)
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.
Thank you Sir!! Like the visual👍
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!
Nicely explained. Love zebra BI visuals.
Hi Jitendra, thank you for stopping by! We promise to keep making new videos in the future 💛
How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?
Very useful and efficient
Thanks A LOT !!!
You're welcome!
Thank you
You're welcome!
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.
Cool earring
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)
Awesome
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😅