DAX近似匹配：获取指定日期及之前最近成本（Power Pivot）

需求说明 #

需要在sales_tbl中添加计算列，从cost_tbl匹配对应ProductID、且Cost Valid From不晚于2023-01-01的最新生效成本值。

实现DAX公式 #

在sales_tbl中创建计算列，使用以下DAX代码：

最近生效成本 = 
VAR CurrentProduct = sales_tbl[ProductID]
VAR MaxValidDate = 
    CALCULATE(
        MAX(cost_tbl[Cost Valid From]),
        FILTER(
            cost_tbl,
            cost_tbl[ProductID] = CurrentProduct &&
            cost_tbl[Cost Valid From] <= DATE(2023, 1, 1)
        )
    )
RETURN
    CALCULATE(
        VALUES(cost_tbl[Cost]),
        FILTER(
            cost_tbl,
            cost_tbl[ProductID] = CurrentProduct &&
            cost_tbl[Cost Valid From] = MaxValidDate
        )
    )

公式逻辑解析 #

1. 定义当前产品变量：CurrentProduct捕获当前行的ProductID，用于后续筛选匹配的成本记录。
2. 筛选最近生效日期：MaxValidDate通过CALCULATE+FILTER，找出同一产品下、生效日期≤2023-01-01的最大日期（即最近的生效时间点）。
3. 获取对应成本值：根据找到的最近生效日期，再次筛选cost_tbl，返回对应的成本数值。

结果验证 #

• 对于ProductID = 0004087：符合条件的最大生效日期为2022-12-10，对应成本37，与示例预期一致。
• 对于ProductID = 6202188：符合条件的最大生效日期为2023-01-01，对应成本283，与示例预期一致。

额外注意事项 #

• 如果同一产品在同一生效日期存在多条成本记录，VALUES会返回错误，此时可替换为MAX(cost_tbl[Cost])确保返回单一值。
• 若某产品无符合条件的成本记录，公式会返回空白，可添加IF(ISBLANK(...), 0, ...)自定义默认值。

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