cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors