Power BI技术问询：如何计算各物料库存耗尽的日期

前提假设 #

先明确两张表的典型结构（若实际结构不同，可对应调整字段名）：

• Stock表：包含物料ID（唯一标识）、当前库存（数值型）字段
• Requirements表：包含物料ID、需求日期（日期型）、当日需求（数值型）字段，无需求的日期可能无对应记录

方法一：DAX计算表实现 #

步骤1：补全连续日期（针对无需求日期的场景） #

如果Requirements表存在日期断档，先生成每个物料的连续日期序列并补全需求为0的记录：

完整需求表 = 
VAR 所有物料 = DISTINCT(Requirements[物料ID])
VAR 日期范围 = CALENDAR(MIN(Requirements[需求日期]), MAX(Requirements[需求日期]))
VAR 物料日期交叉 = CROSSJOIN(所有物料, 日期范围)
RETURN
LEFTJOIN(
    物料日期交叉,
    Requirements,
    "物料ID", "物料ID",
    "需求日期", "需求日期"
)

步骤2：计算累计需求表 #

基于补全后的需求表，计算每个物料从最早日期开始的每日累计需求：

累计需求表 = 
ADDCOLUMNS(
    '完整需求表',
    "累计需求",
    CALCULATE(
        SUM('完整需求表'[当日需求]),
        FILTER(
            ALL('完整需求表'),
            '完整需求表'[物料ID] = EARLIER('完整需求表'[物料ID]) &&
            '完整需求表'[需求日期] <= EARLIER('完整需求表'[需求日期])
        )
    )
)

步骤3：生成库存耗尽日期计算表 #

关联Stock表的库存数据，找到第一个累计需求超过当前库存的日期；若累计总需求始终不超过库存，标记为未耗尽：

库存耗尽日期表 = 
ADDCOLUMNS(
    Stock,
    "库存耗尽日期",
    VAR 物料库存 = Stock[当前库存]
    VAR 物料累计需求集 = FILTER('累计需求表', '累计需求表'[物料ID] = Stock[物料ID])
    VAR 首次超库存日期 = MINX(FILTER(物料累计需求集, '累计需求表'[累计需求] > 物料库存), '累计需求表'[需求日期])
    VAR 累计总需求 = MAXX(物料累计需求集, '累计需求表'[累计需求])
    RETURN
        IF(
            ISBLANK(首次超库存日期),
            IF(累计总需求 <= 物料库存, "未耗尽", BLANK()),
            首次超库存日期
        )
)

方法二：Power Query预处理实现（适合大数据量场景） #

若DAX计算性能不足，可通过Power Query完成数据预处理，再加载为计算表：

let
    // 源数据加载
    需求源 = Requirements,
    库存源 = Stock,
    // 补全连续日期
    所有物料 = List.Distinct(需求源[物料ID]),
    日期范围 = List.Dates(List.Min(需求源[需求日期]), Duration.Days(List.Max(需求源[需求日期])-List.Min(需求源[需求日期]))+1, #duration(1,0,0,0)),
    物料日期交叉 = Table.FromList(所有物料, Splitter.SplitByNothing(), {"物料ID"})
        |> Table.AddColumn("需求日期", each 日期范围)
        |> Table.ExpandListColumn("需求日期"),
    // 合并原需求数据并补全0需求
    完整需求表 = Table.NestedJoin(物料日期交叉, {"物料ID", "需求日期"}, 需求源, {"物料ID", "需求日期"}, "原需求数据", JoinKind.LeftOuter)
        |> Table.ExpandTableColumn("原需求数据", {"当日需求"}, {"当日需求"})
        |> Table.ReplaceValue(null, 0, Replacer.ReplaceValue, {"当日需求"}),
    // 按物料分组计算累计需求
    分组计算累计 = Table.Group(完整需求表, {"物料ID"}, {{"需求数据", each 
        let
            排序日期 = Table.Sort(_, {{"需求日期", Order.Ascending}}),
            累计需求列 = Table.AddColumn(排序日期, "累计需求", (row) => List.Sum(Table.SelectRows(排序日期, (r) => r[需求日期] <= row[需求日期])[当日需求]))
        in
            累计需求列
    }})
    |> Table.ExpandTableColumn("需求数据", {"需求日期", "累计需求"}, {"需求日期", "累计需求"}),
    // 合并库存表并确定耗尽日期
    合并库存 = Table.NestedJoin(分组计算累计, {"物料ID"}, 库存源, {"物料ID"}, "库存数据", JoinKind.LeftOuter)
        |> Table.ExpandTableColumn("库存数据", {"当前库存"}, {"当前库存"}),
    分组找耗尽日期 = Table.Group(合并库存, {"物料ID", "当前库存"}, {{"耗尽日期", each List.Min(Table.SelectRows(_, (r) => r[累计需求] > r[当前库存])[需求日期]), type nullable date}}),
    // 处理未耗尽标记
    最终表 = Table.AddColumn(分组找耗尽日期, "库存耗尽日期", each if [耗尽日期] = null then "未耗尽" else [耗尽日期])
in
    最终表

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