12 month rolling sum

[Anonymous]

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 solution]

Hi @Anonymous ,

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]>=SELECTEDVALUE('Table'[date])&&'Table'[date]<=EDATE(SELECTEDVALUE('Table'[date]),12)))

Best Regards,

Jay

[parry2k]

@Anonymous there are tons of posts on such calculations, here is link to some

Solved: Trailing 12 or Rolling 12 month sum - Microsoft Power BI Community

Rolling 12 Months DAX Measure in Power BI - YouTube

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡