Forum Discussion
Measure based on multiple criteria including date
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 |
- The Date_valid_from identifies the start of a price and a price could change when a later Date_valid_from exists. This implies the measure should find the Price that is valid at the date of the transaction.
- In the Price table more type of Prices exists and the measure should find those of the Price_type “Base”
- And finally the price should be related to the specific Product_ID.
In the data model there is no relationship defined between the two tables.
Anonymous , 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] )
3 Replies
- amitchandak
Super User
Anonymous , 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] )