Search code examples
datepowerbidaxpowerquerydata-analysis

power bi - calculate from 2 tables, with date comparison at the same time

I have 2 tables (csv data below):

price table :

enter image description here

And a fact table :

enter image description here

Here is my question,

Since the price of product can fluctuate during the year, how can I find the specific price for the day in my second table?

csv data here:

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
Solution

  • In DAX, create a Calculated Column in your Fact table with:

    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]
    )

    Then for your total price, create another Calculated Column:

    total price = [price] * [qty sold]