cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## 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 @Siepe ,

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 @Siepe ,

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.

Frequent Visitor

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!

Frequent Visitor

@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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors