cancel
Showing results 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

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).

How can I fix the formula above to get the closest date when we did not find a match?
Thanks,
1 ACCEPTED SOLUTION
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.
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors