PowerBI DAX实现每日加权平均库存定价：递归问题求解

你的核心需求是计算移动加权平均库存价，这类场景无需依赖递归——因为加权价本质是「累计有效库存成本 ÷ 累计有效库存量」，销售操作仅按当前加权价扣减库存金额和数量，不会改变加权价；只有采购会更新加权价。以下提供两种可行方案：

方法一：DAX计算列（实时动态计算） #

适合需要随数据更新自动刷新的场景，先确保交易表包含日期、交易类型（采购/销售）、数量、单价字段，且在Power Query中按日期升序添加索引列。

1. 计算累计采购相关指标 #

累计采购数量 = 
CALCULATE(
    SUM('交易表'[数量]),
    FILTER('交易表', '交易表'[交易类型] = "采购" && '交易表'[索引] <= EARLIER('交易表'[索引]))
)

累计采购成本 = 
CALCULATE(
    SUMX('交易表', '交易表'[数量] * '交易表'[单价]),
    FILTER('交易表', '交易表'[交易类型] = "采购" && '交易表'[索引] <= EARLIER('交易表'[索引]))
)

2. 计算累计销售与当前库存 #

累计销售数量 = 
CALCULATE(
    SUM('交易表'[数量]),
    FILTER('交易表', '交易表'[交易类型] = "销售" && '交易表'[索引] <= EARLIER('交易表'[索引]))
)

当前库存量 = [累计采购数量] - [累计销售数量]

3. 计算加权平均库存价 #

加权平均库存价 = 
VAR 前序索引 = EARLIER('交易表'[索引]) - 1
VAR 前序库存状态 = CALCULATE(
    ROW("库存数量", '交易表'[当前库存量], "加权价", '交易表'[加权平均库存价]),
    '交易表'[索引] = 前序索引
)
VAR 本次采购量 = IF('交易表'[交易类型] = "采购", '交易表'[数量], 0)
VAR 本次采购成本 = IF('交易表'[交易类型] = "采购", '交易表'[数量] * '交易表'[单价], 0)
RETURN
IF(
    [当前库存量] = 0,
    0,
    IF(
        '交易表'[索引] = 0, // 首次交易
        IF('交易表'[交易类型] = "采购", '交易表'[单价], 0),
        IF(
            '交易表'[交易类型] = "采购",
            (前序库存状态[库存数量] * 前序库存状态[加权价] + 本次采购成本) / (前序库存状态[库存数量] + 本次采购量),
            前序库存状态[加权价] // 销售时加权价不变
        )
    )
)

方法二：Power Query迭代预处理（生成静态计算表） #

如果数据更新频率低，或需要提前固化计算结果，用Power Query的迭代功能更直观，避免DAX的复杂变量嵌套：

1. 导入交易数据，按日期升序添加索引列（从0开始）
2. 插入以下M代码（替换表名和字段名）：

let
    源 = Excel.CurrentWorkbook(){[Name="交易表"]}[Content],
    添加索引 = Table.AddIndexColumn(源, "索引", 0, 1, Int64.Type),
    按日期排序 = Table.Sort(添加索引,{{"日期", Order.Ascending}}),
    // 初始化库存状态
    初始状态 = [库存数量=0, 库存成本=0, 加权价=0],
    // 逐行迭代计算
    迭代计算 = List.Accumulate(
        Table.ToRecords(按日期排序),
        {初始状态},
        (累计状态, 当前行) =>
            let
                上一行状态 = List.Last(累计状态),
                新库存数量 = 上一行状态[库存数量] + (if 当前行[交易类型] = "采购" then 当前行[数量] else -当前行[数量]),
                新库存成本 = 
                    if 当前行[交易类型] = "采购" then
                        上一行状态[库存成本] + 当前行[数量] * 当前行[单价]
                    else
                        上一行状态[库存成本] - 当前行[数量] * 上一行状态[加权价],
                新加权价 = 
                    if 新库存数量 = 0 then 0
                    else if 当前行[交易类型] = "采购" then 新库存成本 / 新库存数量
                    else 上一行状态[加权价]
            in
                累计状态 & {[库存数量=新库存数量, 库存成本=新库存成本, 加权价=新加权价]}
    ),
    // 转换为表并合并原数据
    转换表格 = Table.FromList(List.Skip(迭代计算), Splitter.SplitByNothing()),
    扩展字段 = Table.ExpandRecordColumn(转换表格, "Column1", {"库存数量", "库存成本", "加权价"}),
    合并原表 = Table.Join(按日期排序, "索引", 扩展字段, "索引"),
    清理列 = Table.RemoveColumns(合并原表,{"索引"})
in
    清理列

关键逻辑说明 #

• 采购时：加权价 = (上周期库存金额 + 本次采购金额) ÷ (上周期库存量 + 本次采购量)
• 销售时：库存金额和数量按当前加权价同比例扣减，加权价保持不变
• 两种方案均完全匹配你Excel中的递归计算结果，且规避了PowerBI对递归的限制

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