我有 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
在 DAX 中,使用以下命令在事实表中创建计算列:
然后为您的
total price
,创建另一个计算列: