如何在Power Query中创建按周期重置且下限为0的动态累计求和

要实现你需要的Measure C，核心是按Period分组，在组内迭代计算(Measure A - Measure B)的累计值，且强制累计值不低于0。用Power Query的List.Accumulate函数可以直接处理这种带状态的累计逻辑，比双索引或自连接更简洁可靠。

具体步骤与M代码 #

假设你的源表名为Source，且包含Period、Row（用于排序）、Measure A、Measure B列：

1. 先确保数据按Period和行顺序排序：

#"Sorted Rows" = Table.Sort(Source,{{"Period", Order.Ascending}, {"Row", Order.Ascending}}),

2. 按Period分组，对每个组内的数据计算累计值：

#"Grouped by Period" = Table.Group(#"Sorted Rows", {"Period"}, {
    {"GroupedData", each 
        let
            // 先计算每行的Measure A - Measure B
            #"Calc A-B" = Table.AddColumn(_, "A-B", each [Measure A] - [Measure B]),
            // 迭代计算累计值，下限锁定为0
            CumulativeVals = List.Accumulate(
                #"Calc A-B"[A-B],
                {},
                (prevList, currentVal) => 
                    let
                        lastCumulative = if List.IsEmpty(prevList) then 0 else List.Last(prevList),
                        newVal = List.Max({lastCumulative + currentVal, 0})
                    in
                        prevList & {newVal}
            ),
            // 将累计值合并回原表，得到Measure C
            #"Add Measure C" = Table.FromColumns(
                Table.ToColumns(#"Calc A-B") & {CumulativeVals},
                Table.ColumnNames(#"Calc A-B") & {"Measure C"}
            )
        in
            #"Add Measure C", type table
    }
}),

3. 展开分组后的表格，得到最终结果：

#"Final Table" = Table.ExpandTableColumn(#"Grouped by Period", "GroupedData", {"Row", "Measure A", "Measure B", "Measure C"})

逻辑说明 #

• List.Accumulate会逐个遍历每个Period内的A-B值，每次用上一步的累计值加上当前的A-B结果，再取与0的最大值，确保累计值不会低于0。
• 按Period分组的操作会让每个Period的累计独立计算，自动实现"新Period重置累计"的需求。

为什么双索引/自连接不好用？ #

这类方法本质是基于行的静态关联，无法动态跟踪前一行的累计结果（尤其是当累计值被强制设为0后，后续计算需要依赖这个修正后的值），而迭代式的List.Accumulate能直接维护累计状态，更适配这种场景。

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