Excel 265：用DAX度量值实现透视表取各ITEMID最新UNIT_PRICE

前提 #

已通过Power Query将SQL Server的库存交易数据导入Excel 365数据模型，数据包含ITEMID（商品ID）、交易日期、UNIT_PRICE（单价）等字段。

步骤1：创建DAX度量值 #

在数据模型的库存交易表上右键，选择新建度量值，输入以下DAX公式并命名为截止日期最新单价：

截止日期最新单价 = 
VAR 商品最新交易日期 = CALCULATE(MAX('库存交易表'[交易日期]), ALLEXCEPT('库存交易表', '库存交易表'[ITEMID]))
RETURN
CALCULATE(MAX('库存交易表'[UNIT_PRICE]), '库存交易表'[交易日期] = 商品最新交易日期)

公式说明 #

• ALLEXCEPT('库存交易表', '库存交易表'[ITEMID])：保留当前商品ID的上下文，同时应用日期切片器的筛选范围。
• MAX('库存交易表'[交易日期])：获取当前商品在筛选日期范围内的最后交易日期。
• 最后一步根据该日期匹配对应的最新单价，若同一日期有多条记录，取单价的最大值（可替换为AVERAGE/MIN等按需调整）。

步骤2：创建透视表PivotTable1 #

1. 点击插入选项卡 → 数据透视表，选择使用此工作簿的数据模型作为数据源，确认后将透视表重命名为PivotTable1。
2. 在透视表字段面板中：
   • 将ITEMID拖至行区域（透视表默认自动去重，展示唯一商品ID列表）。
   • 将截止日期最新单价拖至值区域。

步骤3：添加日期切片器实现交互筛选 #

1. 选中PivotTable1，点击透视表工具-分析选项卡 → 插入切片器。
2. 在弹出窗口中选择库存交易表的交易日期字段，点击确定。
3. 通过切片器选择单个日期或日期范围，透视表会实时更新每个商品截止所选日期的最新单价。

与SQL+VBA方案的对比 #

• SQL预筛选+VBA方案：需编写聚合SQL提前获取指定日期前的商品最新数据，搭配VBA按钮触发刷新，优点是小数据量下性能较高，但灵活性不足——每次调整日期都需重新执行SQL刷新数据，无法实时交互。
• DAX方案：完全在Excel数据模型内计算，支持切片器实时交互，无需额外刷新操作，适合需要频繁调整日期筛选的场景，且无需编写VBA代码。

内容的提问来源于stack exchange，提问作者Michael.C