topic Re: Calculate Percent Change in DAX Commands and Tips

Calculate Percent Change

natasha519 — Fri, 19 Nov 2021 14:21:39 GMT

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!

Re: Calculate Percent Change

amitchandak — Fri, 19 Nov 2021 15:42:59 GMT

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=6LUBbvcxtKA

Re: Calculate Percent Change

natasha519 — Fri, 19 Nov 2021 15:58:57 GMT

I want YOY change! Also is a date table required? I have month and year columns in the same table

Re: Calculate Percent Change

amitchandak — Fri, 19 Nov 2021 16:24:08 GMT

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

Re: Calculate Percent Change

v-yalanwu-msft — Tue, 23 Nov 2021 03:12:40 GMT

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.

Re: Calculate Percent Change

natasha519 — Mon, 29 Nov 2021 14:59:25 GMT

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

Re: Calculate Percent Change

v-yalanwu-msft — Tue, 30 Nov 2021 02:06:35 GMT

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.

Re: Calculate Percent Change

natasha519 — Tue, 30 Nov 2021 14:51:34 GMT

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.

Re: Calculate Percent Change

v-yalanwu-msft — Wed, 01 Dec 2021 05:24:13 GMT

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.