如何用Excel Power Query按财年（4-3月）重置计算累计成本？

核心思路 #

问题出在之前的全局累计逻辑，我们需要先按财年分组，再在每个财年组内独立计算累计值，这样进入新财年后会自动重置累计。

假设前提 #

• 你的数据表名为表1
• 日期列名为日期，成本列名为成本
• 财年定义：每年4月至次年3月为一个财年（2022年4月为新财年起始）

完整M代码 #

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    // 确保日期列是标准日期格式
    转换日期格式 = Table.TransformColumns(源, {{"日期", Date.From, type date}}),
    // 添加财年分组列：4月及以后归为当年财年，之前归为上一年财年
    生成财年列 = Table.AddColumn(转换日期格式, "财年", each if Date.Month([日期]) >= 4 then Date.Year([日期]) else Date.Year([日期]) - 1),
    // 按财年分组，组内按日期升序排序
    按财年分组排序 = Table.Group(生成财年列, {"财年"}, {{"组内数据", each Table.Sort(_, {{"日期", Order.Ascending}}), type table}}),
    // 组内计算累计成本（高效版：用List.Scan替代逐行筛选）
    组内计算累计 = Table.TransformColumns(按财年分组排序, {"组内数据", 
        (当前组) => 
            let
                成本列表 = 当前组[成本],
                // 用List.Scan迭代计算累计，初始值0，每一步累加当前成本
                累计值列表 = List.Scan(0, 成本列表, (累计值, 当前成本) => 累计值 + 当前成本)
            in
                // 将累计值列表合并回原表
                Table.FromColumns(Table.ToColumns(当前组) & {累计值列表}, Table.ColumnNames(当前组) & {"累计成本"})
    }),
    // 展开分组后的明细数据
    展开明细 = Table.ExpandTableColumn(组内计算累计, "组内数据", {"日期", "成本", "累计成本"}),
    // 调整列顺序（可选，按需调整）
    调整列序 = Table.ReorderColumns(展开明细, {"日期", "财年", "成本", "累计成本"})
in
    调整列序

代码说明 #

1. 转换日期格式：确保日期列是Power Query可识别的日期类型，避免分组出错
2. 生成财年列：根据你设定的财年起始（4月），给每条数据标记所属财年
3. 按财年分组排序：将数据按财年拆分，同时每组内按日期排序，保证累计顺序正确
4. 组内计算累计：用List.Scan高效计算组内累计值，相比逐行筛选的Table.SelectRows，大数据量下性能提升明显
5. 展开明细：将分组后的结果展开为原始行结构，同时带上财年和累计值

自定义适配 #

• 如果你的财年起始月份不是4月，修改Date.Month([日期]) >= 4中的数字即可
• 如果表名、列名不同，替换代码中的表1、日期、成本为你的实际名称

内容的提问来源于stack exchange，提问作者Hm_Joy