PowerBI DAX计算列：按日期条件匹配库存补货订单

需求概述 #

• 目标：创建DAX计算列，标识出可填补库存短缺的对应后续订单
• 现状：已能基于Resch date筛选补货订单，但无法按时间顺序正确匹配到对应短缺行；现有公式无法自动切换至下一个补货订单
• 核心规则：匹配的补货订单需满足：
  • Resch date早于当前短缺发生日期
  • 当前短缺日期不晚于下一个补货订单的Resch date（即该短缺对应到两个补货订单之间的区间）

现有公式（中文注释版） #

当前使用的公式仅能筛选出所有符合条件的最大ID，无法实现顺序匹配：

Upcoming orders  = 
// 仅针对库存短缺行计算（Indicator为"Late"且Change为"-"）
IF([Indicator] = "Late" && [Change] = "-",
    // 筛选Resch date早于当前短缺日期的补货订单，取最大ID
    CALCULATE (
        MAX ( 'Table1'[ID]),
        FILTER ( 'Table1', 'Table1'[Resch date] < [Date] && [Change] = "+" )
    ),
    // 非短缺行返回空值
    BLANK()
)

问题分析 #

现有公式通过MAX('Table1'[ID])直接取所有符合条件的最大订单ID，忽略了时间顺序，无法为每个短缺行匹配对应的、处于正确时间区间的补货订单，也无法自动切换到后续的补货订单。

优化后的DAX公式 #

以下公式通过排序补货订单、划分时间区间，实现按顺序匹配对应短缺行的补货订单：

Upcoming orders = 
VAR CurrentShortageDate = [Date]
// 筛选所有补货订单并按Resch date升序排序
VAR SortedReplenishOrders = 
    SORT(
        FILTER('Table1', 'Table1'[Change] = "+"),
        'Table1'[Resch date], ASC
    )
// 为每个补货订单标记上一个补货订单的日期，划分短缺匹配区间
VAR ReplenishWithIntervals = 
    ADDCOLUMNS(
        SortedReplenishOrders,
        "@PreviousReplenishDate",
        // 获取当前订单之前的最后一个补货订单日期
        CALCULATE(
            MAX('Table1'[Resch date]),
            FILTER(SortedReplenishOrders, 'Table1'[Resch date] < EARLIER('Table1'[Resch date]))
        )
    )
// 找到当前短缺日期所在的补货区间对应的订单ID
VAR MatchingOrderID = 
    MAXX(
        FILTER(
            ReplenishWithIntervals,
            // 短缺日期需在上一个补货订单日期之后（或无之前订单），且在当前补货订单日期之前/当天
            (ISBLANK([@PreviousReplenishDate]) || CurrentShortageDate > [@PreviousReplenishDate]) 
            && CurrentShortageDate <= 'Table1'[Resch date]
        ),
        'Table1'[ID]
    )
RETURN
// 仅在短缺行返回匹配的订单ID，其余行返回空
IF([Indicator] = "Late" && [Change] = "-", MatchingOrderID, BLANK())

效果说明 #

• 该公式会为每个库存短缺行匹配对应的补货订单，确保短缺日期落在上一个补货订单日期之后、当前补货订单日期之前的区间内
• 自动按时间顺序切换匹配的补货订单，解决了原公式无法顺序匹配的问题

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