price list connectivity

[luka_zz]

Hello all,

wonder if you could help me:

In one table I have information about product price which has information about price and date from which the price is valid as shown below. For example product 1 had price 10,0 from January 1 2020 till February 29 2020; from March 1 till June 14 the price was 11,0 and from June 15 onwards it is 12,00.

PRODUCT	PRICE	VALID FROM
Product 1	10,0	1.01.2020
Product 2	15,0	1.01.2020
Product 3	20,0	1.01.2020
Product 1	11,0	1.03.2020
Product 2	15,5	15.02.2020
Product 1	12,0	15.06.2020

In other table I have daily sales of those products.

How can I assign price information from first table to each specific date/product in the second table?

Thank you in advance for your support 

[luka_zz]

@amitchandak works perfectly, thank you very much.

[amitchandak · Accepted solution]

@luka_zz , Create a new column of price in the table like

new column =
var _max = maxx(filter(Price, price[VALID FROM] <=table[Date] && price[PRODUCT] = table[PRODUCT]),price[VALID FROM])
return
maxx(filter(Price, price[VALID FROM] =_max&& price[PRODUCT] = table[PRODUCT]),price[PRICE])