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,

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