Merging with criteria

[mterry]

I have a table with financial information that has multiple dates in it, and another table with conversion rates by month that I would like to merge to the financial table. The problem is that I would like to use some conditions for merging: if there is a project end date in the finance table, then I'd like to use that month for the conversion, otherwise I'd like to use the most recent conversion rate available. What's the most efficient way to do that that will update with data refreshes? Thanks in advance. 

[amitchandak · Accepted solution]

@mterry , In such case we create a calculated column

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

we can add more condition like from currency , to currency

[mterry]

Ok thanks for the reply, I'll try that. Would creating a calculated column make more sense than a DAX measure to achieve the same thing?