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

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

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 于 3个月前 加精
最佳答案
  • 木木
    2018-10-30 12:16:02
    Sum of 7days 
    =CALCULATE (
        SUM ( 'Data'[成交金额] ),
        DATESINPERIOD (
            '日历'[Date],
            MIN ( 'Data'[上市日期] )+ 7 * MIN ( 'CountNo'[序号] )- 7,
            7,
            DAY
        )
    )

    file

回复数量: 21
  • 焦棚子 为了世界和平
    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

    @回头便知

    file
    测试貌似没问题哈

  • 飞天篮球 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)。

    所谓处处留心皆学问......从你最近的两个案例中我也学到了不少。以下是我整理的笔记,供参考学习,感谢提及的各位!!

    file

  • 焦棚子 为了世界和平
    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

  • Excel-幼儿园
    2019-01-10 20:31:54

    @木木 透视表中“总计”结果显示的不对

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter