在powerquery中累积过去12个月的行数据 [英] Cumulating row data over last 12 months in powerquery

问题描述

我正在使用 Excel Powerquery(又名.M)创建仪表板，在其中我需要创建一个度量，该度量需要汇总过去 12 个月的二维值示例:

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:

输入:

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

输出:

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

如果有更多数据可用，也应仅对过去 12 个月进行汇总.感谢任何帮助

Also sum should be done only for last 12 months if more data is available. ANy help is appreciated

推荐答案

我在演示文件中介绍了一个与此非常相似的场景:Power Query demo - Running Total.xlsx

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

您可以从我的 OneDrive 下载并查看步骤:

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

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

基本上，您添加一个索引，按组列"分组(在您的场景 D1 和 D2 中)并创建一个所有行"聚合列.然后复制所有行"列，展开所有行"列，过滤，最后分组依据和总和以创建运行总计.

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"]

这篇关于在powerquery中累积过去12个月的行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！