Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Maybe this question is already asked using different wording that I am not aware of, please feel free to just link me to that if so.
I have managed to make the cumulative sales per year based on the input from the forum. However I experience difficulties in comparing YoY numbers. I constructed following measures
Acc Sales (SEK) = CALCULATE( Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())=Year(Sales[YearMonth]));Sales[YearMonth])
Acc LY Sales (SEK) = CALCULATE( Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())-1=Year(Sales[YearMonth]));Sales[YearMonth])
To use these measures in the correct way, I cannot use the Date hierarcy of the field YearMonth, I have to use the continous date which means I cannot remove the year.
I changed the formula to this, using a month field instead of a date field. And this is where it starts behaving wierd in my eyes.
Acc LY (SEK) = CALCULATE( Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())-1=Year(Sales[YearMonth]));Sales[Month]) Acc LY (SEK) = CALCULATE( Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())-1=Year(Sales[YearMonth]));Sales[Month])
If I use both year and month in cumulates in the correct way, if I remove the year only using month, current years numbers cumulates, LY shows only the total, but cumulates for where there are no data in current year.
Is my question making any sense? What am I doing wrong here?
Regards
VIktor
Solved! Go to Solution.
Hi,
May I suggest you to take a look at the Time patterns at DAX Patterns?. There you will find many examples of calculations like the one you need to perform.
If that does not help, then we can work on your model (for this, I would need a sample PBIX file) but, as of now, your code seems a complex solution for a simple problem, because you are not taking advantage of the standard time intelligence functions.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Hi,
Share some data and show the expected result.
Hi,
I cannot visualise your result actually. Share a dummy dataset which i can paste into Excel and show the expected result.
Hi Avinash,
I have attached two files with dummy data, first file is 2015 vs 2018 where the Difference between these two years is coming fine.
Second file is comparison between 2015 vs 2017 where Difference is not being populated at all. Please let me know if this helps.
Hi,
Share the link from where i can download your PBI file.
Hi,
May I suggest you to take a look at the Time patterns at DAX Patterns?. There you will find many examples of calculations like the one you need to perform.
If that does not help, then we can work on your model (for this, I would need a sample PBIX file) but, as of now, your code seems a complex solution for a simple problem, because you are not taking advantage of the standard time intelligence functions.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Thanks Alberto
It just seems to be so strange that you need to have an "external" date table, and then everything is so simple 😃
Regards
Viktor
Indeed, it is strange. The thing is that with an external table you gain a lot of flexibility in terms of what you can compute, if they did implement it with simpler functions, then it would have been much simpler to write standard calculations, but it would have been very hard to write the advanced ones.
As strange as it is, I think it is a good balance between power and simplicity. Or, if you prefer, I would not know how to make it better, so I take it as a very good choice 🙂
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |