Calculating percent change for each date

[NJ81858]

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!

[Anonymous]

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.