Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like to define measure Base_price: showing the price with Price_type = Base of a specific product at a certain date.
There are two tables:
a Transaction table:
Date | Product_ID | Volume |
29-Mar | D | 45 |
23-Apr | D | 56 |
19-Dec | D | 55 |
a Price table:
Price_type | Product_ID | Price | Date_valid_from |
Base | A | 12 | 01-Feb |
Base | A | 13 | 04-Apr |
Base | D | 27 | 02-Dec |
Base | C | 33 | 23-May |
Gross | A | 14 | 01-Feb |
Base | E | 2 | 13-Mar |
Gross | E | 3 | 27-Mar |
Gross | G | 88 | 04-Jul |
Net | D | 27.5 | 01-Feb |
Net | D | 28 | 13-Mar |
Net | E | 1.8 | 27-Mar |
Net | G | 40 | 07-Apr |
In the data model there is no relationship defined between the two tables.
Solved! Go to Solution.
@BasB , A new column in Transaction Table
price =
var _dt = maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] < Transaction[Date] && Price[Price_type] ="Base"),Price[Date_valid_from] )
return
maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] = _dt && Price[Price_type] ="Base"),Price[Price] )
@BasB , A new column in Transaction Table
price =
var _dt = maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] < Transaction[Date] && Price[Price_type] ="Base"),Price[Date_valid_from] )
return
maxx(filter(Price , Price[Product_ID] =Transaction[Product_ID] && Price[Date_valid_from] = _dt && Price[Price_type] ="Base"),Price[Price] )
Amit,
Thank you, as simple as that.
Transaction table with more records:
Date | Product_ID | Volume |
26-Mar | E | 9 |
29-Mar | A | 2 |
29-Mar | D | 45 |
04-Apr | A | 18 |
23-Apr | A | 3 |
23-Apr | D | 56 |
12-Nov | C | 33 |
04-Dec | A | 123 |
19-Dec | D | 55 |
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |