基于日期范围按物料获取当前单价的DAX实现问题

问题背景 #

现有数据模型包含事实表fact PriceList及维度表dim Date、CustomerPriceGroup、Item，需求为：通过日期切片器（支持单日期/日期范围选择），返回指定物料与价格组的最新生效UnitPrice。

• 示例1：选择2013年6月1日-15日，需返回CustomerPriceGroupID=76、ItemID=14837、UnitPrice=97.92的记录
• 示例2：选择2016年7月1日，需返回对应UnitPrice=113.76的记录

用户尝试的DAX公式未达预期：

Unit Price = 
VAR MinDate = MIN('dim Date'[Date])
VAR MaxDate = MAX('dim Date'[Date])
VAR Result = 
CALCULATE(
    MAX('fact PriceList'[UnitPrice]),
    'fact PriceList'[StartingDate] >= MinDate && 'fact PriceList'[EndingDate] <= MaxDate
)
RETURN Result

问题分析 #

原公式的核心逻辑错误：它仅筛选完全落在切片器日期范围内的价格记录，但实际业务中，价格生效的判定应为：该价格的生效时间段与切片器选择的日期范围存在交集（即StartingDate <= 切片器最大日期且EndingDate >= 切片器最小日期）。此外，原公式直接取UnitPrice的最大值，而非基于生效时间的最新价格，这也不符合需求。

解决思路与修正DAX #

思路步骤 #

1. 提取切片器选择的日期范围[SelectedMinDate, SelectedMaxDate]
2. 筛选出所有与该日期范围存在交集的生效价格记录
3. 对每个CustomerPriceGroupID+ItemID组合，找到其最晚的StartingDate（即最新生效的价格）
4. 匹配该最晚生效日期对应的UnitPrice

修正后的DAX公式（方法1） #

Latest Unit Price = 
VAR SelectedMinDate = MIN('dim Date'[Date])
VAR SelectedMaxDate = MAX('dim Date'[Date])
-- 筛选出与选定日期范围有交集的生效价格记录
VAR ValidPriceRecords = 
    CALCULATETABLE(
        'fact PriceList',
        'fact PriceList'[StartingDate] <= SelectedMaxDate,
        'fact PriceList'[EndingDate] >= SelectedMinDate
    )
-- 计算每个价格组+物料的最新生效日期
VAR LatestStartDatePerGroupItem = 
    ADDCOLUMNS(
        SUMMARIZE(ValidPriceRecords, CustomerPriceGroup[CustomerPriceGroupID], Item[ItemID]),
        "@LatestStart", MAX('fact PriceList'[StartingDate])
    )
-- 匹配最新生效日期对应的价格
RETURN
CALCULATE(
    MAX('fact PriceList'[UnitPrice]),
    TREATAS(
        LatestStartDatePerGroupItem,
        CustomerPriceGroup[CustomerPriceGroupID],
        Item[ItemID],
        'fact PriceList'[StartingDate]
    )
)

简洁版DAX公式（方法2） #

Latest Unit Price = 
VAR SelectedMinDate = MIN('dim Date'[Date])
VAR SelectedMaxDate = MAX('dim Date'[Date])
VAR CurrentGroup = SELECTEDVALUE(CustomerPriceGroup[CustomerPriceGroupID])
VAR CurrentItem = SELECTEDVALUE(Item[ItemID])
-- 筛选当前组+物料的生效价格记录
VAR ValidPrices = 
    FILTER(
        'fact PriceList',
        'fact PriceList'[CustomerPriceGroupID] = CurrentGroup &&
        'fact PriceList'[ItemID] = CurrentItem &&
        'fact PriceList'[StartingDate] <= SelectedMaxDate &&
        'fact PriceList'[EndingDate] >= SelectedMinDate
    )
-- 取生效时间最晚的价格
VAR LatestPrice = 
    MAXX(
        TOPN(1, ValidPrices, 'fact PriceList'[StartingDate], DESC),
        'fact PriceList'[UnitPrice]
    )
RETURN LatestPrice

验证说明 #

以示例1为例：当切片器选择2013年6月1日-15日时，修正后的公式会筛选出所有StartingDate<=2013-06-15且EndingDate>=2013-06-01的价格记录，然后找到CustomerPriceGroupID=76、ItemID=14837对应的最晚StartingDate，最终返回对应的UnitPrice=97.92，符合需求。

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