Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

12 months rolling doesn't work

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

 

PeriodAC/BU/FCScenarioYearAmount
1AC2021            100
2AC2021            210
3AC2021            331
4AC2021            464
5AC2021            611
6AC2021            772
7AC2021            949
8AC2021         1,144
9AC2021         1,358
10AC2021         1,594
11AC2021         1,853
12AC2021         2,138
1AC2022            300
2AC2022            630
3AC2022            993
4AC2022         1,392
5AC2022         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:

 

PeriodAC/BU/FCScenarioYearAmountOutcome
1AC2021            100           100
2AC2021            210           310
3AC2021            331           641
4AC2021            464        1,105
5AC2021            611        1,716
6AC2021            772        2,487
7AC2021            949        3,436
8AC2021         1,144        4,579
9AC2021         1,358        5,937
10AC2021         1,594        7,531
11AC2021         1,853        9,384
12AC2021         2,138     11,523
1AC2022            300     11,723
2AC2022            630     12,143
3AC2022            993     12,805
4AC2022         1,392     13,733
5AC2022         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 rolling
1AC2021            100           100           100                          100
2AC2021            210           310           110                          210
3AC2021            331           641           121                          331
4AC2021            464        1,105           133                          464
5AC2021            611        1,716           146                          611
6AC2021            772        2,487           161                          772
7AC2021            949        3,436           177                          949
8AC2021         1,144        4,579           195                       1,144
9AC2021         1,358        5,937           214                       1,358
10AC2021         1,594        7,531           236                       1,594
11AC2021         1,853        9,384           259                       1,853
12AC2021         2,138     11,523           285                       2,138
1AC2022            300     11,723           300                       2,338
2AC2022            630     12,143           330                       2,558
3AC2022            993     12,805           363                       2,800
4AC2022         1,392     13,733           399                       3,067
5AC2022         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?
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1657506594163.png

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.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1657506594163.png

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

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
Not applicable

@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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.