Forum Discussion
Helper IICalculate Percent Change
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.
Super Usernatasha519 , 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
Helper III want YOY change! Also is a date table required? I have month and year columns in the same table
- amitchandak
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