cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX that looks up value from another table2 and return data where date in table2 <= date in table1

Hello Guy

This problem seems simple but been stuck with it for a bit. I have two table call it Table1 and Table2. I want to create a calculated column in Table1 that picks the exchange rate from table2 where;

1. The dates in Table1 and Table2 Match

2. If there is not date match in the two tables the return the closest earlier date match. I have attached the two tables and the results I would like to achieve with the new calculated column

Looking forward to your assistance, thanks

What is would like to achieve in the new calculated column

2 ACCEPTED SOLUTIONS
Super User

@ouma , Try like

new column in table 1
=
var _date = maxx(filter(Table2, table2[Date] <= table1[Date]), Table2[Date])
return
maxx(filter(Table2, table2[Date] <=_date), Table1[Exchange Rate])

Frequent Visitor

Thanks Amitchandak for your quick response. Your DAX helped me a lot, I improved it as below and it worked

VAR _date =

TOPN (

1,

FILTER (

Table2, table2[Date] <= table1[Date]

),

table2[Date],

DESC

)

RETURN

MAXX (

_maxDate,

Table1[Exchange Rate]

)

2 REPLIES 2
Super User

@ouma , Try like

new column in table 1
=
var _date = maxx(filter(Table2, table2[Date] <= table1[Date]), Table2[Date])
return
maxx(filter(Table2, table2[Date] <=_date), Table1[Exchange Rate])

Frequent Visitor

Thanks Amitchandak for your quick response. Your DAX helped me a lot, I improved it as below and it worked

VAR _date =

TOPN (

1,

FILTER (

Table2, table2[Date] <= table1[Date]

),

table2[Date],

DESC

)

RETURN

MAXX (

_maxDate,

Table1[Exchange Rate]

)

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors