主页 / coding / 问题 / 77878616
Accepted
chris_olv
Asked: 2024-01-25 16:57:34 +0800 CST

power bi - 从 2 个表格计算，同时进行日期比较

  • 772

我有 2 个表（下面是 csv 数据）：

价格表：

在此输入图像描述

还有一个事实表：

在此输入图像描述

这是我的问题，

由于产品的价格在一年中可能会波动，我如何在第二个表中找到当天的具体价格？

csv 数据在这里：

1

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

2

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
date

1 个回答

  1. Best Answer

    在 DAX 中，使用以下命令在事实表中创建计算列：

    price = 
  var thisProduct = [product]
  var thisDate = [date of sale]
  return
    CALCULATE(
      MAX(Price[PRICE]),
      Price[PRODUCT] = thisProduct  &&
      Price[START DATE] <= thisDate  && thisDate  <= Price[END DATE]
    )

    然后为您的total price，创建另一个计算列：

    total price = [price] * [qty sold]
    • 1

相关问题