Thank you Alberto, very creative, learned something new I would have never thought of. Intuitively I would have used the CG-solution. One point from my perspective is worth to be mentioned: The CG group solution is more flexible as you said, because you can write DAX-Code. And a side effect of using a calculated table for the M-N solution is that it only considers the absolute max orderdate whereas the CG-solution could consider a filter on the date table by using ALLSELECTED. That would the M-N solution be unable to implement because it defines the used date ranges on load time of the model. I find this an interesting difference between those two solutions worth to be mentioned.
Amazing insights! To be fair for the calculation group, it has its strengths when you want to build a more interactive report page. It would be just small changes with the calculation group to allow for selecting the last month from a data slicer, whereas the data model solution is static, defined at loading time. You need to be aware in general that when adding interactive or dynamic behavior, calculation times add up or might even mess up efficient storage engine queries. Yes, you can stack up dynamic RLS with some DAX, then allow for switching the language with some DAX supporting to show the right labels, then add dynamic currency conversion including dynamic format strings from a calculation group, all on top of a base measure that might require a virtual table or iterator. But "yes, you can" is not always the smartest decision.
That's great insight on dax query patterns. Well said on it's applicability, test it on YOUR OWN MODEL/MACHINE CONFIG before concluding blindly. Thank you 👍
Thanks for a very interesting video. And for the helpful ideas! One point: in your video, relationships is not many-to-many. Relationships in Power BI file is many-to-one and "Both". As I understand it, this happened by accident due to unique Dates in the DateTable. Many-to-many work just as well :)
Yes, you can create a many-to-many, but the principle is that the relationship between Period and Sales is a many-to-many (obtained through a chain of many-to-one + one-to-many).
very inspirational video as always love your work. I am so novice that even after following the config steps within DAX, I am getting same figures for every period. Could you please help understand what am I doing wrong . thankyou
actually my problem is that when i build relationship between my date table and the issues table [in which i need the period level results], the date columns of these table loose the date hierarchy. How do i fix this ? thankyou
Assuming the last date is 12/31/2023... Last Year = Jan 1 2023 to Dec 31 2023 Prev Year = Jan 1 2022 to Dec 31 2022 Last Month = Dec 1 2023 to Dec 31 2023 Prev Month = Nov 1 2023 to Nov 30 2023 Last 6 Months = Jul 1 2023 to Dec 31 2023 Prev 6 Month = Jan 1 2023 to Jun 30 2023
I love that PowerBI supports such a deep developer experience. My question is a difficult one. I run on a Fiscal Year that begins Sep 1. I need to do most of my reporting with fiscal year and fiscal quarter analysis. While I have a custom date table that include fiscal year number, fiscal quarter, and fiscal month I need to be able to do period over period reporting. How would you suggest modifying either of the approaches you use here to do that? Thank you in advance!
You might just use the regular time intelligence functions. You can find many examples here: www.daxpatterns.com/time-patterns/ You can also use Bravo to generate measures automatically with one of the available templates: bravo.bi
Could you please make a video on this? I have a fact table with start_year (2020) and end_year(2023) parameters in it to limit the number of years worth of data. On top of this I have incremental refresh of last 13 months. I publish this file to web service and change the start_year parameter say from 2020 to 2019. Why does the parameter changes in web service doesn't take into effect? If I change from 2020 to 2021, it does make necessary changes but for the first time only after publishing the file. Any thought on this? My report should only hold only last 5 years of data with last 13 months of incremental refresh.
For me, this has been one of the most complex topics since I learn from you. As always, thank you Alberto... a great lecturer!
Thank you Alberto, very creative, learned something new I would have never thought of. Intuitively I would have used the CG-solution. One point from my perspective is worth to be mentioned: The CG group solution is more flexible as you said, because you can write DAX-Code. And a side effect of using a calculated table for the M-N solution is that it only considers the absolute max orderdate whereas the CG-solution could consider a filter on the date table by using ALLSELECTED. That would the M-N solution be unable to implement because it defines the used date ranges on load time of the model. I find this an interesting difference between those two solutions worth to be mentioned.
Yes! I'm the first to comment. I love your videos. Thank you so much for creating them. I learn so much on each watch. ✅
Amazing insights! To be fair for the calculation group, it has its strengths when you want to build a more interactive report page. It would be just small changes with the calculation group to allow for selecting the last month from a data slicer, whereas the data model solution is static, defined at loading time. You need to be aware in general that when adding interactive or dynamic behavior, calculation times add up or might even mess up efficient storage engine queries. Yes, you can stack up dynamic RLS with some DAX, then allow for switching the language with some DAX supporting to show the right labels, then add dynamic currency conversion including dynamic format strings from a calculation group, all on top of a base measure that might require a virtual table or iterator. But "yes, you can" is not always the smartest decision.
After all these years of telling us to stay away from m2m relationships 😂. I’ll stick to the calc groups. But yea great content as always.
That's great insight on dax query patterns. Well said on it's applicability, test it on YOUR OWN MODEL/MACHINE CONFIG before concluding blindly. Thank you 👍
Of course, all the times!
Nice presentation
Thanks for a very interesting video. And for the helpful ideas!
One point: in your video, relationships is not many-to-many. Relationships in Power BI file is many-to-one and "Both". As I understand it, this happened by accident due to unique Dates in the DateTable. Many-to-many work just as well :)
Yes, you can create a many-to-many, but the principle is that the relationship between Period and Sales is a many-to-many (obtained through a chain of many-to-one + one-to-many).
@@SQLBI Exactly! Now it's even clearer. Thank you!
very inspirational video as always love your work. I am so novice that even after following the config steps within DAX, I am getting same figures for every period. Could you please help understand what am I doing wrong . thankyou
actually my problem is that when i build relationship between my date table and the issues table [in which i need the period level results], the date columns of these table loose the date hierarchy. How do i fix this ? thankyou
Thanks Alberto. What is the difference between previous year and last year ?
Assuming the last date is 12/31/2023...
Last Year = Jan 1 2023 to Dec 31 2023
Prev Year = Jan 1 2022 to Dec 31 2022
Last Month = Dec 1 2023 to Dec 31 2023
Prev Month = Nov 1 2023 to Nov 30 2023
Last 6 Months = Jul 1 2023 to Dec 31 2023
Prev 6 Month = Jan 1 2023 to Jun 30 2023
I love that PowerBI supports such a deep developer experience. My question is a difficult one. I run on a Fiscal Year that begins Sep 1. I need to do most of my reporting with fiscal year and fiscal quarter analysis. While I have a custom date table that include fiscal year number, fiscal quarter, and fiscal month I need to be able to do period over period reporting. How would you suggest modifying either of the approaches you use here to do that?
Thank you in advance!
You might just use the regular time intelligence functions. You can find many examples here: www.daxpatterns.com/time-patterns/
You can also use Bravo to generate measures automatically with one of the available templates: bravo.bi
Could you please make a video on this? I have a fact table with start_year (2020) and end_year(2023) parameters in it to limit the number of years worth of data. On top of this I have incremental refresh of last 13 months. I publish this file to web service and change the start_year parameter say from 2020 to 2019. Why does the parameter changes in web service doesn't take into effect? If I change from 2020 to 2021, it does make necessary changes but for the first time only after publishing the file. Any thought on this? My report should only hold only last 5 years of data with last 13 months of incremental refresh.