cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DataUsurper Frequent Visitor
Frequent Visitor

Previous 12 Months Rolling

Good Morning All,

 

I am attempting to get a DAX formula to calculate the previous 12 months rolling totals, I did reference a prior post on this forum but when I tested it on my data it did not come out as expected.

This image is from the expected results tab, as one can see the months 12/1/18 - 4/1/19 are incorrect

12M.PNG

 

There are two date fields:

  1. From
  2. To

And the following is the formula:

CALCULATE(Sum(Range[SUM(IH_REV)]),DATESBETWEEN(Range[FROMDATE],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Range[TODATE]))),LASTDATE(Range[TODATE])))

Thank you for the help

DU

4 REPLIES 4
DataUsurper Frequent Visitor
Frequent Visitor

Re: Previous 12 Months Rolling

Bump for help

Super User
Super User

Re: Previous 12 Months Rolling

Hi,

Share a dataset and show the expected result.

v-xicai Super Contributor
Super Contributor

Re: Previous 12 Months Rolling

Hi @DataUsurper ,

 

you can try to create measures like DAX below.

 

Period End = LASTDATE(Range[Date])

 

Period Start= FIRSTDATE( DATESINPERIOD(Range[Date], [Period End], -12, MONTH))

 

Rolling 12M Sum = CALCULATE(SUM(Range[SUM(IH_REV)]),DATESBETWEEN ( Range[Date], [Period Start], [Period End] ))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-xicai Super Contributor
Super Contributor

Re: Previous 12 Months Rolling

Hi  @DataUsurper ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy