Forum Discussion
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 value from April 2020 to April 2021?
Any assistance would be appreciated!
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.
8 Replies
natasha519 , You want MOM Change % ?
First Create a date using month and year
Date = "01-"&[Month]&"-" &[Year] //Change data type to date
Join with Date table and use time intelligence
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAI want YOY change! Also is a date table required? I have month and year columns in the same table
natasha519 , if it is year level, you can manage it with a separate year table, but use separate year table
I told in last how to get date from month year. This can done with date or year table
//Only year vs Year, not a level below
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Hi， natasha519 ；
I think it isn't necessary to use another date table. Secondly, do you mean to change the percentage of each state? If yes, I created the following measure according to the screenshot you provided.
fact% = var _last=CALCULATE(SUM([Data Value]),FILTER(ALLEXCEPT('Table','Table'[State N]),[Year]=MAX('Table'[Year])-1&&[Month]="April")) return DIVIDE(SUM([Data Value])-_last,_last)Predicted% = var _last=CALCULATE(SUM([Predicted Value]),FILTER(ALLEXCEPT('Table','Table'[State N]),[Year]=MAX('Table'[Year])-1&&[Month]="April")) return DIVIDE(SUM([Predicted Value])-_last,_last)The final output is shown below:
If not, can you provide simple scenarios (about simple sample screenshots and what you want to output)? Looking forward to your reply！
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.Hello! Apologize for the late response, I changed around the format of the table a bit in Power BI to hopefully make the calculations easier!
There are now only 4 columns which you can see above. I also have created some measure which gives me a 1 if the date was last year (April 2020) and then I have another measure which gives me a 1 if the date is the latest date (April 2021). The last measure is the sum of both of these and gives me a 1 for both April 2020 and now April 2021. I would like to calculate the percent change for the Predicted Value for each recipient from April 2020 to April 2021 and then same for Reported Value.
Essentially I would like a 4th column in this matrix below for percent change for the row
Hi， natasha519 ；
You could create a measure.
Measure = var _2021=CALCULATE(SUM([Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),YEAR([Date])=2021&&MONTH([Date])=4)) var _2020=CALCULATE(SUM([Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),YEAR([Date])=2020&&MONTH([Date])=4)) return IF(ISINSCOPE('Table'[Date]),SUM([Value]),FORMAT( DIVIDE( _2021- _2020,_2020),"0.00%"))The final output is shown below:
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.This makes sense, but how can I incorporate a measure within the formula which already calculates the latest date in 2021 and then 1 year from latest date? The month will not always be 4.
I keep getting the error "A function PLACEHOLDER has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I have one measure called LastYear which returns a 1 for April 2020 currently, and then I have another measure called LatestDate which returns a 1 for April 2021 currently. The last measure is LastYear plus LatestDate which is just the sum of the two measures and returns a 1 for both April 2020 and April 2021.
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.
