Forum Discussion

omaffud's avatar
omaffud
Frequent Visitor
7 years ago
Solved

Lookupvalue on different tables and with values that does not match

Hi, I tried to find a solution for this problems in the forum but I couldn't. I have 2 tables in my BI report, one is SO (Sales Orders) and another one is Exchange Rates. In the sales orders table...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI omaffud ,

    You can try to use following calculate column formula to lookup closed date based on current row contents:

    Formula =
VAR _closedate =
    CALCULATE (
        MAX ( 'Exchange Rates'[Effective Date] ),
        FILTER (
            ALLSELECTED ( 'Exchange Rates' ),
            'Exchange Rates'[Effective Date] <= EARLIER ( 'SO'[Invoice Date] )
                && 'Exchange Rates'[To Curr] = EARLIER ( 'SO'[Order Cur Cod] )
        )
    )
RETURN
    LOOKUPVALUE (
        'Exchange Rates'[Exchange Rate],
        'Exchange Rates'[Effective Date], _closedate,
        'Exchange Rates'[To Curr], 'SO'[Order Cur Cod]
    )

    Regards,
    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI omaffud ,

You can try to use following calculate column formula to lookup closed date based on current row contents:

Formula =
VAR _closedate =
    CALCULATE (
        MAX ( 'Exchange Rates'[Effective Date] ),
        FILTER (
            ALLSELECTED ( 'Exchange Rates' ),
            'Exchange Rates'[Effective Date] <= EARLIER ( 'SO'[Invoice Date] )
                && 'Exchange Rates'[To Curr] = EARLIER ( 'SO'[Order Cur Cod] )
        )
    )
RETURN
    LOOKUPVALUE (
        'Exchange Rates'[Exchange Rate],
        'Exchange Rates'[Effective Date], _closedate,
        'Exchange Rates'[To Curr], 'SO'[Order Cur Cod]
    )

Regards,
Xiaoxin Sheng