Cumulative row data over last 12 months in Power Query

Question

So, I'm working on a dashboard using Excel Powerquery/M. In this, I need to create a measure which requires rolling up values for the past 12 months for 2 dimensions.

Input:

D1     | D2 | MonthYear(D3) | Value
A1       B1     Mar2016      1
A2       B1     Mar2016      2
A3       B1     Mar2016      3
A1       B1     Apr2016      4
A2       B1     Apr2016      5
A3       B1     Apr2016      6
A1       B1     May2016      7
A2       B1     May2016      8
A3       B1     May2016      9

Output:

D1     | D2 | MonthYear(D3) | Value
A1       B1     Mar2016      1
A2       B1     Mar2016      2
A3       B1     Mar2016      3
A1       B1     Apr2016      4+1
A2       B1     Apr2016      5+2
A3       B1     Apr2016      6+3
A1       B1     May2016      7+4+1
A2       B1     May2016      8+5+2
A3       B1     May2016      9+6+3

Also, I need the sum to be done only for the last 12 months.

Answer 1

Basically, what you do is,

•  Add an Index,
•  Group By the "group columns" (in your scenario D1 and D2) 
• Create an "All Rows" Aggregate column. 
• Copy the "All Rows" column
• Expand both "All Rows" columns
• Add Filter 
• Group By and Sum to create the Running Total

The only bit of code is the Added column to produce a true/false column for the filter.

[Index] >= [#"All Rows - Copy.Index"]

Comments

It works quite well except, it is not sum only the last 12 months, but it's rolling ...