Forum Discussion

lkshck's avatar
lkshck
Icon for Helper III rankHelper III
4 years ago
Solved

Lookupvalue based on IF condition

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!

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      lkshck
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        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.