Calculate Percentage change from previous value

[RyanW]

So I have a simple table called MasterData that I am trying to calculate the % difference between the current value and the last previous value

I am using a measure to get the last process date for a particular reference - 

Last Run Date = CALCULATE(MAX( MasterData[ProcessDate] ),FILTER( ALL(MasterData) ,MAXX( FILTER( MasterData, EARLIER( MasterData[ProcessDate] ) < MasterData[ProcessDate] && EARLIER( MasterData[Reference]) = MasterData[Reference]), MasterData[ProcessDate] )))

Which returns

Which is correct, what I now want to to do return the CostPerTon for that Last Run Date and then show the most recent costperton as a % increase/decrease against the costperton for the last run date.

I'm just not sure how should I go about this?  I know its 'should' be simple but I'm going round in circles.

Cheers

[v-huizhn-msft]

Hi @RyanW,

Please create a calculated column to return the CostPerTon for that Last Run Date using the LOOKUPVALUE function as follows.

the last CostPerTon=LOOKUPVALUE(MasterData[CostPerTon], MasterData[ProcessDate], MasterData[Last Run Date])

>>then show the most recent costperton as a % increase/decrease against the costperton for the last run date.

How to show the most recent costperton? In my oppion, the most recent date is equal to the last data. Please share more details for further analysis.

Best Regards,
Angelia

[RyanW]

Thanks for the reply Angelina, 

Unfrotunately that doesn't work in this case and I get the error "A table of multiple values was supplied where a single value was expected."

It would need to take into account the ProcessDate & Reference to return the unique value for that day.  i.e. there can be multiple ProcessDates entries for each date but only one Reference for each relating to a particular date.

The data in my example is just a small extract from the overall data, what I'm actually doing is extracting data from our Sales system, our personnel system and our logistics systems and combining them to give a number of delivery performance dashboards based around our KPI's. 

Thanks

RyanW

[v-huizhn-msft]

Hi @RyanW,

Please create the calculated column using the following formulas.

Column=IF(MasterData[ProcessDate]=MasterData[Last Run Date],MasterData[CostPerTon],0)

the last CostPerTon=CALCULATE(MAX(Column),ALLEXCEPT(MasterData,MasterData[Reference]))

Thanks,
Angelia

[RyanW]

Hi Angelina, 

When I use

Column=IF(MasterData[ProcessDate]=MasterData[Last Run Date],MasterData[CostPerTon],0)

The entire column is populated with 0

If I then try to use 

the last CostPerTon=CALCULATE(MAX(Column),ALLEXCEPT(MasterData,MasterData[Reference]))

I get the errror A circular dependency was detected: MasterData[Column], MasterData[Column 2], MasterData[Column].

The MasterData[Last Run Date] I created was a measure not a column and then just added that to my display which resulted in the table in my first post.

Last Run Date = CALCULATE(MAX( MasterData[ProcessDate] ),FILTER( ALL(MasterData) ,MAXX( FILTER( MasterData, EARLIER( MasterData[ProcessDate] ) < MasterData[ProcessDate] && EARLIER( MasterData[Reference]) = MasterData[Reference]), MasterData[ProcessDate] )))

[v-huizhn-msft]

Hi @RyanW,

>>When I use Column=IF(MasterData[ProcessDate]=MasterData[Last Run Date],MasterData[CostPerTon],0)
The entire column is populated with 0

Why the entire column is 0, you calculated [Last Run Date] by calculated column, there it it ture when MasterData[ProcessDate]=MasterData[Last Run Date]? Do you mind share your .pbix file for further analysis?

Best Regards,
Angelia

[RyanW]

Hi Angelia, I was wondering if you could take a look at the sample pbix I posted?  Thx

[v-huizhn-msft · Accepted solution]

Hi @RyanW,

At first, please note that we can only create measure and calculated column in Power Bi desktop. So I test in Power BI desktop.

After several days' test, I tried many solutions. Finally, I get the previous day's value. Please create a measure using the formula.

Prvious-day-value = Var DD=MasterData[Last Run Date]
RETURN 
CALCULATE(MAX(MasterData[CostPerTon]),FILTER(ALLSELECTED(MasterData),MasterData[ProcessDate]=DD))

Then create a measure to get the increase/decrease percentage.

Percentage = (MAX(MasterData[CostPerTon])-MasterData[Prvious-day-value])/MAX(MasterData[CostPerTon])

Please download the attachments to review more details.

Best Regards,
Angelia

[RyanW]

Hi Angelia,

Perfect!  I've applied this to my own dasboard and it works great, many thanks for all your help!

[RyanW]

Hi Angelia, 

Please see the file in the link below for more information, I have stripped out all the rest of the information and just put in some sample data but it should be enough.  thanks for all your help so far.

https://wetransfer.com/downloads/22a617c9414196984de2069796d8705120170626094835/8d947a716b2a55eb745c...