- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
need generic measure that calculates YOY difference by different columns
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
01-20-2024 12:00 AM | |||
03-29-2024 10:03 AM | |||
04-14-2024 04:35 PM | |||
03-28-2024 04:41 AM | |||
06-18-2024 06:13 AM |
User | Count |
---|---|
123 | |
79 | |
59 | |
58 | |
44 |
User | Count |
---|---|
179 | |
120 | |
82 | |
70 | |
53 |