Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a report that shows total sales for each bucket of products for each sales location across time and I want to create measures that will iterate through the table and find the amount difference from the most recent date value to each of the other date values within the table, as well as find the percent change from the most recent date value to each date value in the table. The trick is that the data for this will update each month, so it needs to be a dynamic solution. This is currently being done in Excel manually, as shown by this screenshot:
In this manual solution, the "Change from Q4" is found by taking the value for 1/25/2023 and subtracting the value for 12/31/2022, and then the "% Change from Q4" column is found by dividing the value for "Change from Q4" by the value for 1/25/2023, or (143,145.12 / 1,019,525.53) in the first row. The same thing is calculated in the "Change from Q3" and "% Change from Q3" columns, only replacing 12/31/2022 with 9/30/2022, so the "Change from Q3" would be (1/25/2023 - 9/30/2022).
Thank you in advance!
Hi @NJ81858
You can refer to the follwing example.
the sample data
Create three measures
Total = SUM('Table'[Value])
Change from = CALCULATE([Total],'Table'[Date]=MAXX(ALLSELECTED('Table'),[Date]))-[Total]
% Change from = DIVIDE([Change from],CALCULATE([Total],'Table'[Date]=MAXX(ALLSELECTED('Table'),[Date])))
Then put the measures to the matrix visual
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |