CODEWITHSUNDEEP
powerquery
user1695083
user1695083

Reputation: 41

Cumulating row data over last 12 months in powerquery

I am creating a dashboard using Excel Powerquery(aka. M), in which I need to create a measure which requires rolling up values for last 12 months for two dimension Example:

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 sum should be done only for last 12 months if more data is available. ANy help is appreciated

Upvotes: 1

Views: 511

Answers (1)

Mike Honey
Mike Honey

Reputation: 15027

I covered a very similar scenario to this in my demo file: Power Query demo - Running Total.xlsx

You can download it from my OneDrive and review the steps:

https://1drv.ms/f/s!AGLFDsG7h6JPgw4

Basically you add an Index, Group By the "group columns" (in your scenario D1 and D2) and create an "All Rows" Aggregate column. Then you Copy the "All Rows" column, Expand both "All Rows" columns, Filter and finally 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, e.g. 

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

Upvotes: 1

Related Questions