12 months rolling doesn't work

[Anonymous]

I have a dataset containing only Cumulative Amounts for each period, like so:

Period	AC/BU/FC	ScenarioYear	Amount
1	AC	2021	100
2	AC	2021	210
3	AC	2021	331
4	AC	2021	464
5	AC	2021	611
6	AC	2021	772
7	AC	2021	949
8	AC	2021	1,144
9	AC	2021	1,358
10	AC	2021	1,594
11	AC	2021	1,853
12	AC	2021	2,138
1	AC	2022	300
2	AC	2022	630
3	AC	2022	993
4	AC	2022	1,392
5	AC	2022	1,832

Now I wan't to calculate a 12 month rolling average (LTM, Last Twelve Months). I found a beautiful example here:  https://community.powerbi.com/t5/Desktop/Moving-sum-last-12-month/m-p/324617

but I can't get it to work. What PowerBI now does, is sum the YTD-figures, like so:

Period	AC/BU/FC	ScenarioYear	Amount	Outcome
1	AC	2021	100	100
2	AC	2021	210	310
3	AC	2021	331	641
4	AC	2021	464	1,105
5	AC	2021	611	1,716
6	AC	2021	772	2,487
7	AC	2021	949	3,436
8	AC	2021	1,144	4,579
9	AC	2021	1,358	5,937
10	AC	2021	1,594	7,531
11	AC	2021	1,853	9,384
12	AC	2021	2,138	11,523
1	AC	2022	300	11,723
2	AC	2022	630	12,143
3	AC	2022	993	12,805
4	AC	2022	1,392	13,733
5	AC	2022	1,832	14,954

So basically, the 12 months-part works but it takes the YTD-figures. What I want is this (last column):

Period	AC/BU/FC	ScenarioYear	Amount	Outcome	Month	12 month rolling
1	AC	2021	100	100	100	100
2	AC	2021	210	310	110	210
3	AC	2021	331	641	121	331
4	AC	2021	464	1,105	133	464
5	AC	2021	611	1,716	146	611
6	AC	2021	772	2,487	161	772
7	AC	2021	949	3,436	177	949
8	AC	2021	1,144	4,579	195	1,144
9	AC	2021	1,358	5,937	214	1,358
10	AC	2021	1,594	7,531	236	1,594
11	AC	2021	1,853	9,384	259	1,853
12	AC	2021	2,138	11,523	285	2,138
1	AC	2022	300	11,723	300	2,338
2	AC	2022	630	12,143	330	2,558
3	AC	2022	993	12,805	363	2,800
4	AC	2022	1,392	13,733	399	3,067
5	AC	2022	1,832	14,954	439	3,359

I have a measure that calculates the 'Month' like this:

SUM(Amount) - CALCULATE(Sum('Table1'[Amount]),DATEADD('Calendar'[Date],-1,MONTH))

However, when I use that instead of 'Amount' in the below formula, the figures are wrong as well. My current formula for LTM is:

EUR LTM = CALCULATE(sum('Table1'[Amount]), FILTER ( ALL ( 'Calendar'),'Calendar'[MonthYear - Sorter] <=MAX ('Calendar'[MonthYear - Sorter]) && 'Calendar'[MonthYear - Sorter] >= MAX('Calendar'[MonthYear - Sorter]) -11 ))

Where [MonthYear - Sorter] is my [MonthID].

Can anyone help me figure out how to tweak this formula to get it to work?

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, Month is a measure you created, so you can't use it in the SUM function which only accepts columns as parameter.

I create a sample and here's my solution, use the DATESINPERIOD and SUMX functions.

Create a measure.

EUR LTM =
SUMX (
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -12, MONTH ),
    'Table1'[Month]
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft ,

thanks for the effort in helping me out!

I've tried the solution you offered, but can't get the values to work. Somehow I can't open the PBIX you sent (incompatible with my current version... we have an outdated system sadly 😐), but I've now included my own PBIX (can't add to a post yet, so uploaded it to WeTransfer), so perhaps you can check what I'm dping wrong?

Thanks for helping me out!

https://wetransfer.com/downloads/2a864a46b515b0cf10b9297cfb64673120220711085738/ab60a63fc15f727846f0...

[Anonymous]

@v-yanjiang-msft , I have to correct myself: it works like a charm! I had a error in my 'Month' formula which caused it to go wrong in January of each year ; once I fixed that, it was perfect.

Thanks for the effort!