Active Heads each month, rolling 12 months

[karlin78]

Hi,

I need some help with calculating Rolling 12 month NetAmount compared to previous Rolling 12 month NetAmount. I'm able to get the Rolling 12 monh NetAmount correct (i e month 1-12) with the following DAX:

Rolling 12m Net Amount = CALCULATE(SUM(absalon_salesSet[Net Amount]);DATESINPERIOD(absalon_salesSet[Month end];LASTDATE(absalon_salesSet[Month end]);-1;YEAR))

And I also got the Previous Year Net Amount correct (i e month Jan - Dec) with the following DAX:

Previous Year Net Amount = calculate(sum(absalon_salesSet[Net Amount]);PARALLELPERIOD(absalon_salesSet[Month end];-12;month))

But I need help getting a Rolling 12m Net Amount figure for month 13 - 24. Can someone help me it would be much appreciated!
Best regards

Karin

[v-shex-msft]

Hi @karlin78,

Based on your description, you want to get the next year total amount, right?

If this is a case, you can try to use below formula:

Next YTD = CALCULATE(SUM('Fact table'[Amount]),PARALLELPERIOD('Fact table'[Date],1,YEAR))

Regards,

Xiaoxin Sheng

[karlin78]

Hi, 

Thanks for your help!
Howevere this is a solution to my problem.
For clarify; now it's end May-17. I'd like to compare the Rolling 12 months NetAmount for May2017 - June 2016 (month 1-12) with Rolling 12 months NetAmount for May2016 - June 2015 (month 13-24).

Does anyone have a solution to my problem?

I'd like to combine the Measures "Rolling12month NetAmount" (month 1-12) with "Previous year" (but Previous year not calender Year but Rolling12month NetAmount (month 13-24)

Best regards

Karin

[v-shex-msft]

Hi @karlin78,

I think it is hard to let external filter directly works on measure which has an internal filter.

In my opinion, you can simple modify the original formula to let it calcualte previous 2 year data.

Rolling 12m Net Amount = CALCULATE(SUM(absalon_salesSet[Net Amount]);DATESINPERIOD(absalon_salesSet[Month end];LASTDATE(absalon_salesSet[Month end]);-2;YEAR))

BTW, if your original measure not contains any internal filter, you can simply use calculate with external filter to achieve your requirement.

Regards,

Xiaoxin Sheng