% change over time by product ID

[Pavlhs90]

Hello All,

I am trying to write a measure that will calculate percentage change of price overtime (from oldest to most recent date) by product ID. The measure should be comparing the price of a product on a date with the price of the same product on the exact previous date.

The outcome should be the following:

Product_ID	Date		Price		% change
1	2020-01-01		10
1	2020-04-01		15		50%
1	2022-08-15		30		100%
2	2015-01-01		50
2	2019-06-01		25		-50%
3	2018-01-01		50
3	2020-06-01		15		-70%
3	2022-08-15		30		100%
3	2023-08-15		90		200%
3	2024-09-01		110		22%

So far I have the following:

Percent Change =
var _pre=MAXX(FILTER(ALL('TABLE'),[Start Date]= CALCULATE(MAX([Start Date]),FILTER(ALL('TABLE'),[Start Date]<MAX('TABLE'[Start Date])))),[Price])
return DIVIDE(MAX([Price])- _pre,_pre)

This only seems to work when I filter the dataset on 1 product at a time.

Any ideas?

Regards
Pav

[Pavlhs90]

@lbendlin  Thank you!!

[lbendlin · Accepted solution]

This is the standard "Penultimate"  pattern.  Just a couple of steps, no need to bring out the big guns ( "ALL"  is very loud). Note this will only work if the data aggregated to the product and date levels. (well, technically the product level don't matter much).  Note that my  % change formula is slightly different from yours.  There are some weird and wonderful things happening when the adjacent data values have different signs...