Cumulative row data over last 12 months in Power Query

0 votes

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.

Apr 1 in Power BI by Shubham
• 13,290 points
172 views

1 answer to this question.

0 votes

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"]
answered Apr 1 by Upasana
• 8,530 points

Related Questions In Power BI

0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18 in Power BI by Cherukuri
• 19,990 points
243 views
0 votes
1 answer

Is there a way to convert JSON data to readable table in power bi

Follow these steps step 1 - Click on ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,090 points
1,955 views
0 votes
1 answer

Dax query in power bi

Here is my table before I have ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 14,090 points
68 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,290 points
115 views
0 votes
1 answer

Power Query: Adding a Special Case

Try adding a Group By with Minimum ...READ MORE

answered Dec 27, 2018 in Power BI by Upasana
• 8,530 points
30 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14 in Power BI by Upasana
• 8,530 points
20 views
0 votes
1 answer

Replace “yes” found in one list with the actual string from another list

Objective : The idea is to use List.Transform twice, ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,530 points
39 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
277 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,530 points
906 views
0 votes
1 answer

Looping in Power Query

In Power Query, you often find a ...READ MORE

answered Jan 30 in Power BI by Upasana
• 8,530 points
216 views