Last 12 months rolling sum of measure

[Laurix]

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!

[Laurix]

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!

[amitchandak · Accepted solution]

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