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 we have the revenue generated in a specific date (Invoice Date) and in a specific currency (Order Cur Code). I am trying to build a Dax formula to get the exchange rate for that specific date and currency in the Exchange rates table, when it is not USD. So far, what I got is:

XR = IF(
'SO'[Order Cur Cod]="USD",1,
LOOKUPVALUE('Exchange Rates'[Exchange Rate],
'Exchange Rates'[Effective Date],'SO'[Invoice Date],
'Exchange Rates'[To Curr],'SO'[Order Cur Cod]))
 
However, in the exchange rate table, sometimes I do not have a date that matchs the invoice date (see picture below). 
 
 
 
 
 
 
How can I fix the formula above to get the closest date when we did not find a match?
Thanks,
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

1 Reply

  • 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