Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm trying to write a measure that filters out future values when compairing YOY difference, and I was able to do this. However, when I use that measure against customer, my total is correct by at the custome level the calcuation is not correct. How do I modify my measure so that I the difference can be calculated across different columns and be correct? Thank you.
https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing
Solved! Go to Solution.
Hi,
You may download my PBI file from here. Dragged Year and Month from the seperate columns of the Calendar Table rather than from the hierarchy.
Hope this helps.
What is the problem? In the Customer Table, the measure [diff YOY amount] returns the correct result in each row and in the Grand Total column.
you are correct I did a little more looking and the everything is calculating as expected. I've since upgraded the file and now have a diffenent problem.
I wanted to apply a % increase to revenue last year and wanted to calculate a target based on last year's results.
I did this by creating a key in the 'calendar' table with a RELATED function to a table I called 'growth by month %' which contains the growth by month. after I calcute this target, this target should sum up at the bottom but it does not. Not sure why. link to the file is below.
https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing
Hi,
You may download my PBI file from here. Dragged Year and Month from the seperate columns of the Calendar Table rather than from the hierarchy.
Hope this helps.
it worked, but I don't understand why, would you be willing to explain what you did? I am very much a novice so I really want to understand this so I can apply it to other tasks. Thanks.
hi bo_wang5
you need to change the context..
try the measure below in the matrix with the red square.
measure = SUMX(VALUES(revenue[Customer]),revenue[revneue] - revenue[revenue ly])
Best regards.
I've updated my google drive file with your measure suggestion.
https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing
and the following is the output.
when I slice the data to august (YTD), it is showing the correct calculation for revenue-revnue ly, in all rows except customer 3, the expected value there should be -31652.83.
the goal is to create a measure that shows basically ytd difference reguardless of which column i'm operating on. the measure I have now YOY diff simple is just subtracting revenue ly from revenue as a check.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |