Vlookup the latest price

[Anonymous]

Hello Team,

I need help creating a new column in a table.

My tables are as follows:

Table Stock_main:

Date	Description	Balance
28/01/2022	Sugar	5.00
28/01/2022	Sugar	4.00
28/01/2022	Soda	3.00
27/01/2022	Chicken	7.00
26/01/2022	Chicken	5.00

Purchasing table:

Date	des	Unit price
03/01/2022	Sugar	10.00
01/01/2022	Sugar	8.00
01/01/2022	Soda	15.00
01/01/2022	Chicken	100.00
31/12/2022	Chicken	110.00

I need to vlookup the latest unit price in the table Stock_main from the purchasing table.

I appreciate your help.

Kind Regards,

Hasvine

[amitchandak · Accepted solution]

@Anonymous , a new column in Stock_main

MAxx(filter(Purchasing, Purchasing[des] =Stock_main[Description] && Purchasing[Date] <=Stock_main[Date]) ,Purchasing[Date]  )

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

It required bit of modelling where you need to create a calender table and join these table with it.

Then create a column as below:-

latest_unit_Price = 
VAR _max_date =
    CALCULATE (
        MAX ( 'Purchasing table'[Date] ),
        FILTER (
            'Purchasing table',
            'Purchasing table'[description] = Stock_main[Description]
        )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Purchasing table'[Unit price] ),
        FILTER ( 'Purchasing table', 'Purchasing table'[Date] = _max_date )
    )

Refer a file below:-

[amitchandak · Accepted solution]

@Anonymous , a new column in Stock_main

MAxx(filter(Purchasing, Purchasing[des] =Stock_main[Description] && Purchasing[Date] <=Stock_main[Date]) ,Purchasing[Date]  )