Rolling 13 Months Sum DAX

[mohinderbabu]

Hi friends,

I am trying to create Rolling 13 months sum using DAX and I am looking for some help around it.

This is my data set. And I want to create a measure and not a column which gives me Rolling 13 months sale for the month I select in the filter.

Data Set:

Month Year	Sale
Apr-21	1000
May-21	200
Jun-21	3000
Jul-21	1200
Aug-21	1500
Sep-21	1600
Oct-21	1300
Nov-21	2000
Dec-21	1000
Jan-22	2000
Feb-22	3000
Mar-22	1000
Apr-22	1000
May-22	2000
Jun-22	1000

Result Expected:

Month Year	Rolling 13 Month Sale
Apr-22	19800
May-22	20800
Jun-22	21600

Thanks

[danextian · Accepted solution]

Hi @mohinderbabu ,

This measure below will work if you have a date column in your table. It won't work if you have only the month name as it is a text.

Measure = 
CALCULATE(
    SUM( 'Table'[Sale] ),
    DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -13,  MONTH )
)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mohinderbabu]

Thanks Ashish for your help and time.

[Ashish_Mathur]

You are welcome.

[danextian · Accepted solution]

Hi @mohinderbabu ,

This measure below will work if you have a date column in your table. It won't work if you have only the month name as it is a text.

Measure = 
CALCULATE(
    SUM( 'Table'[Sale] ),
    DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -13,  MONTH )
)

[mohinderbabu]

Hi mate, thans for your time and help, it worked.