Forum Discussion
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
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])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
- amitchandak
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])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]
)