【叼钻！慎入！】PQ、DAX 怎样实现这种日期分组汇总？

Power Query ⋅ 回头便知 ⋅ 于 2018-10-30 00:08:46 ⋅ 最后回复由 Excel-幼儿园于 2019-01-10 20:31:54 ⋅ 4571 阅读

file
（实际的数据源当然比上图中的要多，时间段有些SKU我也分了最多有30周）

以下是测试数据源，可复制到Excel中：

产品编号	上市日期	销售日期	成交金额
1001	2018/1/1	2018/1/2	888
1001	2018/1/1	2018/1/3	666
1001	2018/1/1	2018/1/4	515
1001	2018/1/1	2018/1/5	827
1001	2018/1/1	2018/1/6	610
1001	2018/1/1	2018/1/7	717
1001	2018/1/1	2018/1/8	712
1001	2018/1/1	2018/1/9	752
1001	2018/1/1	2018/1/10	837
1001	2018/1/1	2018/1/11	743
1001	2018/1/1	2018/1/12	695
1001	2018/1/1	2018/1/13	743
1001	2018/1/1	2018/1/14	730
1001	2018/1/1	2018/1/16	623
1001	2018/1/1	2018/1/17	749
1001	2018/1/1	2018/1/18	828
1001	2018/1/1	2018/1/19	787
1001	2018/1/1	2018/1/20	938
1001	2018/1/1	2018/1/21	762
1001	2018/1/1	2018/1/22	831
1001	2018/1/1	2018/1/23	758
1001	2018/1/1	2018/1/24	983
1001	2018/1/1	2018/1/25	788
1001	2018/1/1	2018/1/26	728
1001	2018/1/1	2018/1/27	761
1001	2018/1/1	2018/1/28	875
1001	2018/1/1	2018/1/29	877
1001	2018/1/1	2018/1/30	634
1001	2018/1/1	2018/1/31	890
1001	2018/1/1	2018/2/1	505
1002	2018/1/10	2018/1/15	662
1002	2018/1/10	2018/1/16	666
1002	2018/1/10	2018/1/17	621
1002	2018/1/10	2018/1/18	770
1002	2018/1/10	2018/1/19	696
1002	2018/1/10	2018/1/20	562
1002	2018/1/10	2018/1/21	837
1002	2018/1/10	2018/1/22	647
1002	2018/1/10	2018/1/23	627
1002	2018/1/10	2018/1/24	851
1002	2018/1/10	2018/1/25	606
1002	2018/1/10	2018/1/26	887
1002	2018/1/10	2018/1/27	929
1002	2018/1/10	2018/1/28	838
1002	2018/1/10	2018/1/30	781
1002	2018/1/10	2018/1/31	965
1002	2018/1/10	2018/2/1	606
1002	2018/1/10	2018/2/2	604
1002	2018/1/10	2018/2/3	583
1002	2018/1/10	2018/2/4	506
1002	2018/1/10	2018/2/5	764
1002	2018/1/10	2018/2/6	849
1002	2018/1/10	2018/2/7	930
1002	2018/1/10	2018/2/8	878
1002	2018/1/10	2018/2/9	510
1002	2018/1/10	2018/2/10	685
1002	2018/1/10	2018/2/11	658
1002	2018/1/10	2018/2/12	971
1002	2018/1/10	2018/2/13	593
1002	2018/1/10	2018/2/14	645

本帖已被设为精华帖！

本帖由 Alex 于 5年前加精

最佳答案

木木

2018-10-30 12:16:02

Sum of 7days 
=CALCULATE (
    SUM ( 'Data'[成交金额] ),
    DATESINPERIOD (
        '日历'[Date],
        MIN ( 'Data'[上市日期] )+ 7 * MIN ( 'CountNo'[序号] )- 7,
        7,
        DAY
    )
)

file

回复数量: 21

焦棚子微软 MVP
⋅ 2018-10-30 11:07:36

这中国式报表需求着实了得。

木木

⋅ 2018-10-30 12:16:02

Sum of 7days 
=CALCULATE (
    SUM ( 'Data'[成交金额] ),
    DATESINPERIOD (
        '日历'[Date],
        MIN ( 'Data'[上市日期] )+ 7 * MIN ( 'CountNo'[序号] )- 7,
        7,
        DAY
    )
)

file

飞天篮球 Talk is cheap, show me your code.

⋅ 2018-10-30 13:30:38

PQ解法，常规思路，供参考！
把代码中的“T = #table(n,{m[b]})”这部分写成“T = #table(n,{D,m[b]})”可以观察到具体的哪些7天。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 
    更改 = Table.TransformColumnTypes(源,{{"上市日期", type date}, {"销售日期", type date}}),
    分组 = Table.Group(更改, "产品编号", {"a", (z)=>[ m = Table.Group( Table.Buffer(Table.Sort(Table.Combine({z,
                                                                                                             #table({"销售日期"},
                                                                                                                    List.Generate( ()=>z[上市日期]{0},
                                                                                                                                   each _<=List.Max(z[销售日期]),
                                                                                                                                   each Date.AddDays(_,1),
                                                                                                                                   each {_})
                                                                                                                     ) } ),
                                                                                              "销售日期" ) ),
                                                                      "销售日期",
                                                                     {"b",each List.Sum([成交金额])},
                                                                      0,
                                                                     (x,y)=>Number.From(Date.AddDays(x,7)<=y) ),
                                                    D = List.Transform(m[销售日期],each Text.Format("#{0}-#{1}",{_,Date.AddDays(_,6)})),
                                                    n = List.Transform({1..List.Count(m[b])},each Number.ToText(_,"第0个7天")),
                                                    T = #table(n,{m[b]})]
                                                    [T]
                                        },1),
    展开 = Table.ExpandTableColumn(分组, "a", List.Max( List.Transform(分组[a],each {Table.ColumnNames(_),Table.ColumnCount(_)}),
                                                       null,
                                                       each _{1}){0} )
in
    展开

Ntt docomo

⋅ 2018-10-30 14:10:37

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"上市日期", type date}, {"销售日期", type date}}),
    分组的行 = Table.Group(更改的类型, {"产品编号"}, {{"Q", each [M=List.Combine(List.Accumulate(Table.ToRecords(_),{{},{0}},(x,y)=> [A=List.Count(x{0})+1,B=if y[销售日期]<Date.AddDays(y[上市日期],7*A) then {x{0},{x{1}{0}+y[成交金额]}} else {x{0}&{x{1}{0}},{0+y[成交金额]}}][B])),K=Record.FromList(M,List.Transform({1..List.Count(M)},each "第"&Text.From(_)&"周"))][K]}}),
    #"展开的“Q”" = Table.ExpandRecordColumn(分组的行, "Q", {"第1周", "第2周", "第3周", "第4周", "第5周", "第6周"})
in
    #"展开的“Q”"

DTer
⋅ 2018-10-30 15:19:56

厉害厉害各位大佬
小信
⋅ 2018-10-31 10:40:57

let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
改类型 = Table.TransformColumnTypes(源,{{"上市日期", type date}, {"销售日期", type date}}),
分组 = Table.Group(改类型,"产品编号",{"a",each
[a=Table.Group(,"销售日期",{"aa",each List.Sum([成交金额])},0,
(x,y)=>Number.From(Number.RoundDown(Number.From(x-[上市日期]{0})/7,0)<>
Number.RoundDown(Number.From(y-[上市日期]{0})/7,0))),
b=Table.FromRows({a[aa]},List.Transform({1..List.Count(a[销售日期])},each Number.ToText(,"第0周")))][b]
}),
展开 = Table.ExpandTableColumn(分组,"a",List.Union(List.Transform(分组[a],each Table.ColumnNames(_))))
in
展开
回头便知
⋅ 2018-11-01 09:36:42

@木木
感谢大神帮忙！简单明了，高效实用！请问一下，能否把拉出来的结果表，直接在PBI Desktop中生成一张新表？我还不太会写，感谢！
回头便知
⋅ 2018-11-01 10:02:50

@飞天篮球
非常感谢大神指导！写得太专业了！
可以实现自动扩展，完全自动化！M语言与Excel配合真是一对！
回头便知
⋅ 2018-11-01 10:06:43

@Ntt docomo
感谢回复指导！可惜只考虑了6周，M语言主要考虑的是自动化……
回头便知
⋅ 2018-11-01 10:12:50

@小信感谢回复指导。这是另一种思路，似乎更简洁，但执行时出错，暂未测试成功。
回头便知
⋅ 2018-11-01 10:16:56

@焦棚子感谢回复，这其实不算是中国式报表，确实是一种分析需求。大神能否指导下：用木木回复的DAX生成度量值后，能否把拉出来的结果表，直接在PBI Desktop中生成一张新表？应该怎么写？感谢感谢！
小信
⋅ 2018-11-01 10:20:47

@回头便知

测试貌似没问题哈
飞天篮球 Talk is cheap, show me your code.
⋅ 2018-11-01 10:43:37

@回头便知

最优解自然是木木老师的DAX解法。

M在加载大数据到表的时候确实很慢。IMHO：

1、ntt的List.Accumulate写法挺好。最后一步没有实现动态扩展，这只是个小问题，改一下就好；

2、小信的写法没有考虑全面，比如上市之后头七天或者当中的某七天没有成交额，输出结果就跟你说的不一样了。
当然，他的动态扩展列用List.Union(List.Transform(分组[a],Table.ColumnNames))，写法更简洁,值得学习。

Abv fyi!
回头便知
⋅ 2018-11-01 10:52:00

@小信
感谢回复，看到你的截图了，原来复制过来时漏了_，已自行修改，效果很不错，代码简洁。
也请参考「飞天篮球」大神的回复，他是专业评述～
回头便知
⋅ 2018-11-01 10:55:18

@飞天篮球
学无止境。各施各法，只要方法能满足目前的需求才是实用的，没有永远适用的完美算法～。不过你讲的M不适合大数据量处理，近期深有体会，如果数据量多，还是要通过DAX处理更高效！
再次感谢大神！也感谢PBIhub提供的优秀平台，很方便交流与学习！
飞天篮球 Talk is cheap, show me your code.
⋅ 2018-11-01 12:14:45

@回头便知
老夫晚生后学，承蒙畅神不弃，引路入门(M)。

所谓处处留心皆学问......从你最近的两个案例中我也学到了不少。以下是我整理的笔记，供参考学习，感谢提及的各位！！
焦棚子微软 MVP
⋅ 2018-11-01 14:22:27

@回头便知
看到各位大神都回复了，特别是木木老师的@木木，已不敢造次。
都特别好，个人提个建议，可以从思维上改变下中国式报表的布局相关，这样更好些。
木木
⋅ 2018-11-02 08:01:46

@回头便知 PBI中使用矩阵，和透视表一样进行布局就可以
雀二娃
⋅ 2018-11-05 17:28:43

资质是在太愚钝，居然看了半个小时才看懂木木的DAX
klzb
⋅ 2018-11-06 14:14:23

let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
自定义1 = Table.Group(源,"产品编号",{"a",each Table.Transpose(Table.FromColumns({List.Transform(Table.Group(Table.AddColumn(Table.RemoveColumns(,"产品编号"),"期间",each Date.DayOfYear([销售日期])-Date.DayOfYear([上市日期])+1),"期间",{"b",each },0,(x,y)=>Number.From(Number.RoundUp(y/7)<>Number.RoundUp(x/7)))[b],each List.Sum([成交金额]))}))}),
自定义2 = Table.FromColumns({Table.ToColumns(自定义1){0}}&Table.ToColumns(Table.Combine(Table.ToColumns(自定义1){1})),{"产品编码"}&List.TransformMany({1..Table.ColumnCount(Table.Combine(自定义1[a]))},each {null},(x,y)=>"第"&Text.From(x)&"周"))
in
自定义2