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
Icon for Microsoft Employee rankMicrosoft 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

RyanW's avatar
RyanW
Frequent Visitor
9 years ago

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

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

     

    • RyanW's avatar
      RyanW
      Frequent Visitor
      9 years ago

      Hi Angelia,

       

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