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, 2019 in Power BI by Shubham
• 13,370 points
473 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, 2019 by Upasana
• 8,570 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, 2019 in Power BI by Cherukuri
• 32,420 points
1,763 views
0 votes
0 answers
0 votes
0 answers

Creating Stacked Column Chart without unpivot of data in Power Query

​Hello, For creating Stacked Column Chart into PBI, ...READ MORE

1 day ago in Power BI by Prakash
• 140 points
12 views
+1 vote
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
• 17,380 points
3,513 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,570 points
61 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, 2019 in Power BI by Upasana
• 8,570 points
51 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, 2019 in Power BI by Upasana
• 8,570 points
58 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,200 points
614 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,570 points
1,963 views
0 votes
1 answer

Looping in Power Query

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

answered Jan 30, 2019 in Power BI by Upasana
• 8,570 points
620 views