12 month rolling sum

Question

I'm currently trying to get a 12 month rolling sum for each month.

Year	Month	Count	12 Month Rolling
2017	November	20	20
2017	December	25	25
2018	January	30	30
2018	February	45	45
2018	March	22	22
2018	April	50	50
2018	May	35	35
2018	June	0	0
2018	July	5	5
2018	August	10	10
2018	September	40	40
2018	October	36	36
2018	November	14	14
2018	December	20	20
2019	January	31	31
2019	February	20	20

The measure I'm using to get this is:

12 Month Rolling= 
VAR NumberofMonths= 12
VAR LastCurrentDate= MAX (Sheet1[Date])
VAR Period = DATESINPERIOD(Sheet1[Date],LastCurrentDate,-NumberofMonths,YEAR)
VAR Result = CALCULATE((DISTINCTCOUNT(Sheet1[Count])),Period)
VAR FirstDateInPeriod = MINX(Period,Sheet1[Date])
VAR LastDateWithSales = MAX(Sheet1[Date])

RETURN
IF(FirstDateInPeriod <= LastDateWithSales,Result)

However I was hoping to have the sum in the 12 month rolling column like this:

Year	Month	Count	12 Month Rolling
2017	November	20	332
2017	December	25	332
2018	January	30	338
2018	February	45	328
2018	March	22	283
2018	April	50	261
2018	May	35	211
2018	June	0	176
2018	July	5	176
2018	August	10	171
2018	September	40	161
2018	October	36	121
2018	November	14	85
2018	December	20	71
2019	January	31	51
2019	February	20	20

Any help on getting this rolling sum over 12 months would be greatly appreciated.

Thanks in advance

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
You could add a date column in the table.
date = DATE('Table'[Year],'Table'[number of Month],1)
Then create a measure as below.
Measure = CALCULATE(SUM('Table'[Count]),FILTER(ALLSELECTED('Table'),'Table'[date]&gt;=SELECTEDVALUE('Table'[date])&amp;&amp;'Table'[date]&lt;=EDATE(SELECTEDVALUE('Table'[date]),12)))

&nbsp;
Best Regards,
Jay

Forum Discussion

12 month rolling sum

2 Replies

Related content

Rolling 3 Month Sum

rolling 3 months sum

3 months rolling sum

12-month rolling cumulative

Rolling 12 month sum