Forum Discussion
Power ParticipantRolling 12 month Total
I currently have the below that displays a SUM of all invoices. This worked great whilst we only had 1 years (12 months) worth of data, but now we have moved into 2021, we now have this years and last years values (currently 15months - Jan 2020 to March 2021). How can i get it so the measure will only SUM the last 12 calendar months worth of invoices.
StuartSmith , Check these examples with date table. In one case date from the table has been used. Another one used date from date table.
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
when you select one date and need a trend for 12 months
https://www.youtube.com/watch?v=duMSovyosXE
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
2 Replies
- amitchandak
Super User
StuartSmith , Check these examples with date table. In one case date from the table has been used. Another one used date from date table.
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
when you select one date and need a trend for 12 months
https://www.youtube.com/watch?v=duMSovyosXE
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH)) 
StuartSmithThank, with your suggestions, I was able to get it working by using the below...
Last 12m Spend =Var CurrentDate = TODAY()Var PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-12, DAY(CurrentDate))Var Result = CALCULATE(SUM('MDSL Output'[Net Amount]), DATESINPERIOD('MDSL Output'[Invoice Date], MAX('MDSL Output'[Invoice Date]), -12, MONTH))ReturnResult