Cumulative Total/Rolling Sum Last 12 months

[himanshu56]

Hi All,

I have a requirement where in I have to show rolling 12 month sum against a date column.

I have mentioned the requirement in the below table.

Need: I need a DAX which gives me the expected result as mentioned below. Please note: I want this as a column and not as a measure

Requirement:  For any given month, I need addition of that month's YTD count till YTD counts of -11 months

Please have a look at the table below to understand the requirement.

@amitchandak  @mahoneypat 

YearMonth (Date Format)	YTD ( This is a column. Not a measure)	Expected Result ( As a calculated Column not a measure)
Jan-18	253	253
Feb-18	202	253+202
Mar-18	238	238+202+253
Apr-18	206	and so on till Dec-18
May-18	240
Jun-18	237
Jul-18	271
Aug-18	245
Sep-18	214
Oct-18	270
Nov-18	243
Dec-18	206
Jan-19	260	But from here onwards it should give addition of 260(which is Jan 19 YTD) count all the way till 202 (which is Feb 18 YTD count)
Feb-19	226	Similarly here addition of 226 (Feb 19 YTD) all the way till 238 ( March -18)
Mar-19	237	237 till 206
Apr-19	229	and so on
May-19	260	till end of data
Jun-19	215
Jul-19	225
Aug-19	240
Sep-19	202
Oct-19	240
Nov-19	235
Dec-19	225	For eg. Here addition of 225 + 235+240+202+240+225+215+260+229+237+226+260. 260 being Jan19 count
Jan-20	237
Feb-20	264
Mar-20	266	Basically sum of this months data till -11 months.
Apr-20	260
May-20	229
Jun-20	231
Jul-20	237
Aug-20	226
Sep-20	219
Oct-20	201
Nov-20	212
Dec-20	234

[himanshu56]

It worked like a charm. Thanks a lot for your help on this

[v-yiruan-msft · Accepted solution]

Hi @himanshu56 ,

First, please create a date dimension table Calendar.

Calendar = CALENDARAUTO()

Then create a calculated column as below:

Rolling Sum Last 12 months =
CALCULATE (
    SUM ( 'Table'[YTD] ),
    ALL ( 'Table' ),
    DATESBETWEEN (
        'Calendar'[Date],
        DATEADD ( LASTDATE ( 'Calendar'[Date] ), -11, MONTH ),
        LASTDATE ( 'Calendar'[date] )
    )
)