Measure based on multiple criteria including date

[BasB]

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.

[amitchandak · Accepted 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]

Amit,

Thank you, as simple as that.

[BasB]

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