Date Comparison Rolling X months to previous period

[TakeoffGoatMigo]

I need to do a rolling 3, 6, and 12 month average that compares to the previous period of same length.  This would mean last 3 months compared to 3 months before, last 6 months compared to the 6 months before, etc.

I have tried using the ParallelPeriod function in DAX, but wasn't able to figure it out entirely.

What series of DAX formulas would I need to compare the last 3 completed months with the 3 completed onths before?  Once that logic is set, I can apply to the other time periods.

Thanks!

[v-easonf-msft]

Hi， @TakeoffGoatMigo 

Make sure you have a calendar table, then you can try formula like:

Rolling Average =
CALCULATE (
    SUM ( Sheet1[Sales] ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -3, MONTH )
) / 3

Best Regards,
Community Support Team _ Eason

[TakeoffGoatMigo]

This is what I had for the past 3 months, but how do I get the 3 months before that to compare to? 

Thank you!

[v-easonf-msft · Accepted solution]

Hi, @TakeoffGoatMigo 

You just need to modified the 'start date' in function "DATESINPERIOD" .

New measure:

Rolling Average 3 months before = 
CALCULATE (
    SUM ( financials[Sales] ),
    DATESINPERIOD ( 'Calendar'[Date],EDATE( MAX ( 'Calendar'[Date] ),-3), -3, MONTH )
) / 3

Please check my attached pbix for more details.

Best Regards,
Community Support Team _ Eason