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

Frequent Visitor

## Last 12 months rolling sum of measure

Hi everybody!

I have a problem which should be simple, however I don't know why I cannot get the correct result.

I have a table called 'Reporting' with two columns: [Report date] and [Accumulated value]. The [Accumulated value] column is in fact a rolling sum  which resets every January.

The table with the desired output (last two columns) is

 Report Date Accumulated value Monthly value(current month - previous month) Last 12 months sum of Monthly values (including current month) 1-Jan-2019 100 100 1-Feb-2019 150 50 1-Mar-2019 180 30 1-Apr-2019 220 40 1-May-2019 270 50 1-Jun-2019 300 30 1-Jul-2019 310 10 1-Aug-2019 330 20 1-Sep-2019 370 40 1-Oct-2019 400 30 1-Nov-2019 460 60 1-Dec-2019 500 40 1-Jan-2020 50 50 450 1-Feb-2020 110 60 460 1-Mar-2020 150 40 470 1-Apr-2020 170 20 450 1-May-2020 200 30 430 1-Jun-2020 220 20 420 1-Jul-2020 270 50 460 1-Aug-2020 290 20 460 1-Sep-2020 370 80 500 1-Oct-2020 400 30 500 1-Nov-2020 440 40 480 1-Dec-2020 490 50 490

For the [Monthly Value] measure I get the correct results using the formula

 Monthly Value = if(MONTH(MAX(Reporting[Report Date]))=1,SUM(Reporting[Accumulated value]),SUM(Reporting[Accumulated value])-CALCULATE(sum(Reporting[Accumulated value]),PREVIOUSMONTH('Calendar'[Date])))

For the [Last 12 months sum] measure I'm trying with the formula

 Last 12 months sum = CALCULATE([Monthly Value] ,DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]),-12,MONTH))

however instead of the needed value, I'm getting the rolling sum of the [Accumulated value] column (100, 250, 430 etc.).

What am I doing wrong?

Any help would be highly appreciated.

Thank you!

1 ACCEPTED SOLUTION
Super User

@Laurix , Create measure like

Current Value =
var _max = EOMONTH(max('Date'[Date]),-1)
var _min1  = EOMONTH(max('Date'[Date]),-2) +1
var _min = if( YEAR(_min1) <> YEAR(max('Date'[Date])) ,_max+1, _min1)
return
CALCULATE(SUM(Data[Accumulated value]), DATESMTD('Date'[Date])) -CALCULATE(SUM(Data[Accumulated value]),DATESBETWEEN('Date'[Date],_min, _max))

rolling 12 =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), WINDOW(-11,REL,0,REL,ALL('Date'[Month year sort],'Date'[Month Year]),ORDERBY('Date'[Month year sort])))

or

rolling 12 DP =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), DATESINPERIOD('Date'[Date], max('Date'[Date]), -12, MONTH))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

2 REPLIES 2
Frequent Visitor

Thank you, @amitchandak !

My formula for the Current Value was also returning the correct values, however the SUMX for the rolling sum for the last 12 months did the trick (I knew I needed a SUMX there, it was that simple...).

All the best!

Super User

@Laurix , Create measure like

Current Value =
var _max = EOMONTH(max('Date'[Date]),-1)
var _min1  = EOMONTH(max('Date'[Date]),-2) +1
var _min = if( YEAR(_min1) <> YEAR(max('Date'[Date])) ,_max+1, _min1)
return
CALCULATE(SUM(Data[Accumulated value]), DATESMTD('Date'[Date])) -CALCULATE(SUM(Data[Accumulated value]),DATESBETWEEN('Date'[Date],_min, _max))

rolling 12 =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), WINDOW(-11,REL,0,REL,ALL('Date'[Month year sort],'Date'[Month Year]),ORDERBY('Date'[Month year sort])))

or

rolling 12 DP =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), DATESINPERIOD('Date'[Date], max('Date'[Date]), -12, MONTH))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

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.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors