Rolling 21 Month Sum per Month

[Anonymous]

Hi All,

I am trying to get a rolling 12 month total for each specfic month in my rows. Attached you will see:

Column A is YearMonth. This is the month of refernce.

Column B is the Rolling 12 start, i.e. 12 months prior.

Column C is the Rolling 12 end date. I made this one month after our refernce month so I could use "<" in my calculation.

Column D is my sales per reference month. so 2018 April had 177 units. May had 186, etc.

I am trying to show a new column, lets call it column E, that sums all the prior 12 months of sales for the reference month. For example, 2016 January would only show 220 since we have no prior data. But 2016 December would have 2405, 2017 January would have 2383, etc. 

[v-piga-msft · Accepted solution]

Hi @Anonymous,

For your scenario, you could refer to my test pbix which has been attached.

1. You need create the start calculated column with the formula below.

start = DATE(LEFT('Table'[Rolling 12 start],4),RIGHT('Table'[Rolling 12 start],2),1)

2. You need a calendar table and create the relationship with the original table.

3. You could create the measure below.

total = CALCULATE(SUM('Table'[Sales 12M]))
rolling 12 month total = CALCULATE([total],FILTER(ALLSELECTED('Table'),'Table'[start]<=MAX('Table'[start])&&'Table'[start]>DATE(YEAR(MAX('Table'[start]))-1,MONTH(MAX('Table'[start])),1)))

Here it the output.

Best Regards,

Cherry