Power BI中基于双表日期匹配的产品对应价格计算问询

我有两张表格（CSV数据如下）：

价格表 #

PRODUCT;PRICE;START DATE;END DATE
A;10;01/01/2023;01/07/2023
A;11;02/07/2023;31/12/2023
B;12;01/01/2023;01/07/2023
B;13;02/07/2023;31/12/2023
C;14;01/01/2023;01/07/2023
C;15;02/07/2023;31/12/2023
D;16;01/01/2023;01/07/2023
D;17;02/07/2023;31/12/2023
E;18;01/01/2023;01/07/2023
E;19;02/07/2023;31/12/2023
F;20;01/01/2023;01/07/2023
F;21;02/07/2023;31/12/2023
G;22;01/01/2023;01/07/2023
G;23;02/07/2023;31/12/2023
H;24;01/01/2023;01/07/2023
H;25;02/07/2023;31/12/2023
I;26;01/01/2023;01/07/2023
I;27;02/07/2023;31/12/2023
J;28;01/01/2023;01/07/2023
J;29;02/07/2023;31/12/2023

销售事实表 #

product;date of sale;qty sold;total price
A;02/05/2023;24;QTY SOLD * PRICE for this specific day
A;03/06/2023;25;QTY SOLD * PRICE for this specific day
B;04/07/2023;26;QTY SOLD * PRICE for this specific day
B;05/08/2023;27;QTY SOLD * PRICE for this specific day
C;06/09/2023;28;QTY SOLD * PRICE for this specific day
C;07/10/2023;29;QTY SOLD * PRICE for this specific day
D;08/11/2023;30;QTY SOLD * PRICE for this specific day
D;09/12/2023;31;QTY SOLD * PRICE for this specific day
E;10/01/2023;32;QTY SOLD * PRICE for this specific day
B;11/02/2023;33;QTY SOLD * PRICE for this specific day
C;12/03/2023;34;QTY SOLD * PRICE for this specific day
C;13/04/2023;35;QTY SOLD * PRICE for this specific day
D;14/07/2023;36;QTY SOLD * PRICE for this specific day
D;15/08/2023;37;QTY SOLD * PRICE for this specific day
J;16/12/2023;38;QTY SOLD * PRICE for this specific day

由于产品价格在年内会波动，如何在Power BI中为事实表的每条销售记录匹配对应日期的产品价格？

方法一：Power Query合并查询（推荐用于数据预处理） #

1. 导入并校准数据格式：将两张CSV导入Power BI后，进入Power Query编辑器，先确认所有日期列（START DATE、END DATE、date of sale）为日期类型——如果显示为文本，选中对应列后点击「转换」选项卡→「数据类型」→「日期」。
2. 设置多条件合并：
   • 选中销售事实表，点击「主页」选项卡→「合并查询」→「合并查询作为新查询」。
   • 在合并窗口右侧选择价格表作为匹配对象，设置三组匹配规则：
     • 销售表product ↔ 价格表PRODUCT（匹配同一产品）
     • 销售表date of sale ≥ 价格表START DATE（销售日期在价格生效起始日之后）
     • 销售表date of sale ≤ 价格表END DATE（销售日期在价格生效截止日之前）
   • 合并类型选择「左外部」（保留销售表所有记录），点击确定。
3. 提取价格并计算总价：
   • 合并后新增的列会显示匹配的价格表行，点击列标题右侧的展开按钮，仅勾选PRICE列，确定后即可得到每条销售记录对应的价格。
   • 新增自定义列，输入公式[qty sold] * [PRICE]，替换原有的total price列即可。

方法二：DAX计算列（适合在数据视图中直接处理） #

1. 确认日期格式：在数据视图中检查所有日期列是否为日期类型，若不是则转换为日期类型。
2. 创建价格匹配列：在销售事实表中新建计算列，使用以下DAX公式：

   对应价格 = 
   CALCULATE(
       VALUES('价格表'[PRICE]),
       FILTER(
           '价格表',
           '价格表'[PRODUCT] = '销售事实表'[product] &&
           '销售事实表'[date of sale] >= '价格表'[START DATE] &&
           '销售事实表'[date of sale] <= '价格表'[END DATE]
       )
   )
   

   该公式会筛选出与当前销售记录产品一致、且销售日期落在价格生效区间内的价格值。
3. 计算实际总价：再创建一个计算列计算销售总价：

   实际总价 = '销售事实表'[qty sold] * '销售事实表'[对应价格]
   

注意事项 #

• 确保价格表中同一产品的价格生效区间无重叠、无间隙，否则可能出现匹配错误或空值。
• 若销售日期不在价格表的任何区间内，计算列会返回空值，可通过IFERROR函数添加默认值（比如IFERROR(上述公式, 0)）。

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