Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.