cancel
Showing results 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

Anonymous
Not applicable

## 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:

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
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.

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.

3 REPLIES 3
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.

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

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!

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!

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors