Lookupvalue based on IF condition

[lkshck]

Hey,

I'm currently having two tables. The first contains contract data with a Contract start date, SalesOrg (just a number), Product name. I created a new custom column called Cost, which will gather data price with LOOKUPVALUE from the second table. The second table contains the following columns: SalesOrg, Price, Product name, Date (date where the price is valid from). Now we had a price increase starting from 01.07.2022 which is also reflected in the second table. So we have a price for the products before 01.07.2022 and after it. 

Now the goal is to have the Custom column "Cost" in the contract table which reflects the valid price for the contracts based on the contract start date. So if a contract has a start date before 01.07.2022 it should contain the old price and if it's after the 01.07.2022 it should contain the new price. Any idea how we can get this solved?

Thanks in advance!

[MajeedKhan]

I am trying to visualize your problem. i made the following 2 table, PRICE AND CONTRACT.

if my tables created correctly, can you show me how you want to map the cost.

you can also give me values of the missing cells so i can imagine better

[lkshck]

Hey, thanks for the fast response. I made it visible with the Lolly pop as product name. So this one was bought at 28.06.2022 with price of 100$/€. But from the price table you can see that there was a price increase for every contract which starts at 01.07.2022. So every new contract for Lolly pop should show the new price 120$/€.

[v-jingzhang · Accepted solution]

Hi @lkshck 

You can create a calculated column with below code in the contract table. 

Cost = 
VAR _priceValidDate =
    MAXX (
        FILTER (
            PriceTable,
            PriceTable[Price Valid Date] <= ContractTable[Contract Start Date]
                && PriceTable[Product Name] = ContractTable[Product Name]
        ),
        PriceTable[Price Valid Date]
    )
RETURN
    MAXX (
        FILTER (
            PriceTable,
            PriceTable[Price Valid Date] = _priceValidDate
                && PriceTable[Product Name] = ContractTable[Product Name]
        ),
        PriceTable[Price]
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.