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,380 points
715 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,590 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,460 points
3,427 views
0 votes
0 answers

Creating Stacked Column Chart without unpivot of data in Power Query

h READ MORE

Jan 21 in Power BI by Prakash
• 140 points

closed Mar 24 by Sirajul 54 views
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

Jan 21 in Power BI by Prakash
• 140 points
112 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
• 18,040 points
6,237 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,590 points
75 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,590 points
187 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,590 points
75 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
896 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,590 points
3,309 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,590 points
1,465 views