Forum Discussion
Computing Difference between latest MAT (Moving Annual Total) and MAT for previous 12 months
Hi Community, I'm in troubles and hope you can help me!
I'm trying to calculate de growth rate between MAT for the last 12 months for sales data and the MAT for the previous 12 months before the first ones. This not works because this calculation cannot find any data and is dividing by zero so the result is Infinite.
For example I need to see, the growth rate between MAT sales on Novembrer 2018 and November 2017, which is -37.63% (Sales_MAT-Sales_MAT-2)/Sales_MAT-2 but i'm in the situation as you can see in the screenshot.
MAT and MAT-2 Sales DAX formua are actually:
Sales_MAT = CALCULATE(SUM(Sales), DATESINPERIOD(Date_Table[Date], LASTDATE(Date_Table[Date]),-1,YEAR))
Sales_MAT-2 = CALCULATE(SUM(Sales), DATESINPERIOD(Date_Table[Date], DATEADD(LASTDATE(Date_Table[Date]),-1,YEAR),-2,YEAR))
I need to place data for Nov17 next to Nov18 to accomplish this? Could you help me?
Thanks!
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
4 Replies
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/434008Awesome! Glad it was useful. It's hard to cover all the different ways you might use it so I opted to just go with a basic scenario and let people adapt it to their needs!
Hi Anonymous ,
I'd like to suggest you to use date function to manually set up calculation date range instead time intelligence functions.
If you are confused on coding formula , please share some sample data for test.
How to Get Your Question Answered Quickly
BTW, your year month fields seem not like really date format.
Regards,
Xiaoxin Sheng