Lookupvalue on different tables and with values that does not match

[omaffud]

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,

[v-shex-msft · Accepted solution]

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