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

     

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft 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
    Microsoft 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

    • RyanW's avatar
      RyanW
      Frequent Visitor
      9 years ago

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

        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