Forum Discussion

natasha519's avatar
natasha519
Icon for Helper II rankHelper II
4 years ago
Solved

Calculate Percent Change

I'm pretty new to Power BI - I have a dataset that has Month, Year, Predicted Value, and Data Value all in their own columns. How would I calcuate the percent change for the predicted value and Data ...
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， natasha519 ；

    Please try modify it.

    Measure = 
var _last=CALCULATE( LASTDATE('Table'[Date]),ALL('Table'))
var _2021=CALCULATE(SUM([Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),EOMONTH([Date],0)=EOMONTH(_last,0)))
var _2020=CALCULATE(SUM([Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),EOMONTH([Date],0)=EOMONTH(_last,-12)))
return IF(EOMONTH(MAX([Date]),0)=EOMONTH(_last,0)||EOMONTH(MAX([Date]),0)=EOMONTH(_last,-12), IF(ISINSCOPE('Table'[Date]),SUM([Value]),FORMAT( DIVIDE( _2021- _2020,_2020),"0.00%")))

    The final output is shown below:

    The formula is dynamic, the last month in the table is the same month in the previous year. If you still have errors, could you please take a screenshot to see the error prompt and more details?
    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi， natasha519 ；

Please try modify it.

Measure = 
var _last=CALCULATE( LASTDATE('Table'[Date]),ALL('Table'))
var _2021=CALCULATE(SUM([Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),EOMONTH([Date],0)=EOMONTH(_last,0)))
var _2020=CALCULATE(SUM([Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),EOMONTH([Date],0)=EOMONTH(_last,-12)))
return IF(EOMONTH(MAX([Date]),0)=EOMONTH(_last,0)||EOMONTH(MAX([Date]),0)=EOMONTH(_last,-12), IF(ISINSCOPE('Table'[Date]),SUM([Value]),FORMAT( DIVIDE( _2021- _2020,_2020),"0.00%")))

The final output is shown below:

The formula is dynamic, the last month in the table is the same month in the previous year. If you still have errors, could you please take a screenshot to see the error prompt and more details?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.