如何用M代码或DAX基于工单日期计算每日积压量？

一、M代码预处理数据 #

先清洗原始CSV的脏数据，标准化每个工单的状态时间线：

• 筛选并去重每个工单的动作记录，剔除重复的In/Out Date条目
• 为每个工单生成状态时间区间：标记动作对应的工单状态（在库/转出/关闭/重开）
• 处理空值：空的In Date默认取工单创建时间（若有该字段），空的Out Date用后续动作的In Date或当前日期填充

示例M代码片段：

let
    源 = Csv.Document(File.Contents("你的工单文件路径.csv"), [Delimiter=",", Encoding=65001]),
    提升标题 = Table.PromoteHeaders(源, [PromoteAllScalars=true]),
    转换类型 = Table.TransformColumnTypes(提升标题, {{"工单ID", type text}, {"动作类型", type text}, {"In Date", type date}, {"Out Date", type date}}),
    // 按工单ID分组并按时间排序动作记录
    按工单分组 = Table.Group(转换类型, {"工单ID"}, {{"动作记录", each Table.Sort(_, {"In Date", Order.Ascending})}}),
    // 生成每个工单的状态区间
    生成状态区间 = Table.AddColumn(按工单分组, "状态区间", each 
        let
            动作表 = [动作记录],
            // 标记工单状态
            添加状态 = Table.AddColumn(动作表, "状态", each 
                if List.Contains({"创建", "重开", "退回"}, [动作类型]) then "在库"
                else if List.Contains({"关闭", "转出"}, [动作类型]) then "不在库"
                else "未知"
            ),
            // 用下一条动作的In Date填充当前空的Out Date
            填充后续日期 = Table.AddColumn(添加状态, "下一个In日期", each List.Skip(添加状态[In Date], Table.PositionOf(添加状态, _)+1){0}),
            修正空OutDate = Table.ReplaceValue(填充后续日期, null, DateTime.Date(DateTime.LocalNow()), Replacer.ReplaceValue, {"Out Date"}),
            替换空值 = Table.ReplaceValue(修正空OutDate, null, [下一个In日期], Replacer.ReplaceValue, {"Out Date"})
        in 替换空值
    ),
    // 展开所有状态区间数据
    展开状态区间 = Table.ExpandTableColumn(生成状态区间, "状态区间", {"In Date", "Out Date", "状态"})
in
    展开状态区间

二、DAX计算每日积压量 #

基于M代码预处理后的表格，通过DAX创建度量值统计每日在库工单数量：

1. 先建立日期表（覆盖所有需要统计的日期范围）：

日期表 = CALENDAR(MIN('预处理工单表'[In Date]), MAX('预处理工单表'[Out Date]))

2. 创建积压量度量值：

每日积压量 = 
VAR 当前日期 = MAX('日期表'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT('预处理工单表'[工单ID]),
    FILTER(
        ALL('预处理工单表'),
        '预处理工单表'[In Date] <= 当前日期 &&
        '预处理工单表'[Out Date] > 当前日期 &&
        '预处理工单表'[状态] = "在库"
    )
)

核心逻辑说明 #

• M代码阶段完成数据清洗+状态标准化，解决In/Out Date空值、重复问题，将每个工单的状态变化转化为明确的时间区间
• DAX阶段通过日期表关联，判断每个日期下哪些工单处于「在库」状态（即In Date≤当前日期且Out Date>当前日期），去重统计工单ID数量——重开/退回的工单会生成新的「在库」区间，自动被计入对应日期的积压量

内容的提问来源于stack exchange，提问作者Serge Inácio