Forum Discussion
Comparing the same month from last year
Hello,
I need to calculate the percentage difference of the 'Ratio' column (Column B) for the same month from last year.
For example: Comparing the 'Ratio' from 202007 (10) to 201907 (11) would be a 9.5% Difference.
Any help would be appreciated. Many Thanks.
| YYYY | Ratio | Date |
| 202007 | 10 | 1/07/2020 |
| 202006 | 12 | 1/06/2020 |
| 201907 | 11 | 1/07/2019 |
| 201906 | 15 | 1/06/2019 |
Hi davidr
Create a new column as below
_LOOK =var _val = LOOKUPVALUE('Table'[Ratio], 'Table'[YYYY], FORMAT(left('Table'[YYYY],4),"####")-1 & right('Table'[YYYY],2))//RETURN _valRETURN if(ISBLANK(_val),BLANK(),(_val-'Table'[Ratio])/_val)Below is the Output, I don't see 9.5%, you may change the calculation in the above column creation dax formula marked in blue to achieve your desired result.
5 Replies
davidr - There are time "intelligence" functions for this like CALCULATE([somthing],PREVIOUSYEAR())
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008- You can try this DAX measure...SPLY =VAR _SPLYValue = CALCULATE(sum(LastYear[Ratio]), SAMEPERIODLASTYEAR(LastYear[Date]))VAR _ThisYearValue = SUM(LastYear[Ratio])VAR _Diff = _SPLYValue-_ThisYearValueRETURNIF(_Diff>0,DIVIDE(_Diff,_SPLYValue,0)*100, "")Thanks and hit the like button,Mohammed Adnan
davidr , you can use time intelligence and date table
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) diff = [MTD Sales]-[last year MTD Sales] diff % = divide([MTD Sales]-[last year MTD Sales],[last year MTD Sales])Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eTo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Hi davidr
Create a new column as below
_LOOK =var _val = LOOKUPVALUE('Table'[Ratio], 'Table'[YYYY], FORMAT(left('Table'[YYYY],4),"####")-1 & right('Table'[YYYY],2))//RETURN _valRETURN if(ISBLANK(_val),BLANK(),(_val-'Table'[Ratio])/_val)Below is the Output, I don't see 9.5%, you may change the calculation in the above column creation dax formula marked in blue to achieve your desired result.
