cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## % change over time by product ID

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

1 ACCEPTED SOLUTION
Super User

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...

2 REPLIES 2
New Member

@lbendlin  Thank you!!

Super User

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...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.