I have a dataset containing only Cumulative Amounts for each period, like so: PeriodAC/BU/FCScenarioYearAmount1AC2021 1002AC2021 2103AC2021 3314AC2021 4645AC2021 6116AC2021 7727AC2021 9498AC2021 1,1449AC2021 1,35810AC2021 1,59411AC2021 1,85312AC2021 2,1381AC2022 3002AC2022 6303AC2022 9934AC2022 1,3925AC2022 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/324617but I can't get it to work. What PowerBI now does, is sum the YTD-figures, like so: PeriodAC/BU/FCScenarioYearAmountOutcome1AC2021 100 1002AC2021 210 3103AC2021 331 6414AC2021 464 1,1055AC2021 611 1,7166AC2021 772 2,4877AC2021 949 3,4368AC2021 1,144 4,5799AC2021 1,358 5,93710AC2021 1,594 7,53111AC2021 1,853 9,38412AC2021 2,138 11,5231AC2022 300 11,7232AC2022 630 12,1433AC2022 993 12,8054AC2022 1,392 13,7335AC2022 1,832 14,954 So basically, the 12 months-part works but it takes the YTD-figures. What I want is this (last column): PeriodAC/BU/FCScenarioYearAmountOutcomeMonth12 month rolling1AC2021 100 100 100 1002AC2021 210 310 110 2103AC2021 331 641 121 3314AC2021 464 1,105 133 4645AC2021 611 1,716 146 6116AC2021 772 2,487 161 7727AC2021 949 3,436 177 9498AC2021 1,144 4,579 195 1,1449AC2021 1,358 5,937 214 1,35810AC2021 1,594 7,531 236 1,59411AC2021 1,853 9,384 259 1,85312AC2021 2,138 11,523 285 2,1381AC2022 300 11,723 300 2,3382AC2022 630 12,143 330 2,5583AC2022 993 12,805 363 2,8004AC2022 1,392 13,733 399 3,0675AC2022 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?

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

Not applicable

4 years ago

Solved

12 months rolling doesn't work

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?

Need Help

v-yanjiang-msft
4 years ago
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.
12 months rolling doesn't work.pbix31 KB

3 Replies

v-yanjiang-msft
Community Support
4 years ago
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.
12 months rolling doesn't work.pbix31 KB
- Anonymous
  Not applicable
  4 years ago
  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/ab60a63fc15f727846f0573c54de843b20220711085811/546242
  - Anonymous
    Not applicable
    4 years ago
    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!

Forum Discussion

12 months rolling doesn't work

3 Replies

Related content

12-month rolling cumulative

Rolling accumulation

Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc

Rolling accumulation

Rolling 12 Month calculation