Forum Discussion

RyanW's avatar
RyanW
Frequent Visitor
9 years ago
Solved

Calculate Percentage change from previous value

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

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

     

9 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      RyanW
      Frequent Visitor
      9 years ago

      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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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