Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
omaffud
Frequent Visitor

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). 
2019-04-02_13-33-05.jpg
 
 
 
 
 
 
How can I fix the formula above to get the closest date when we did not find a match?
Thanks,
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.