Forum Discussion
Merging with criteria
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.
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
2 Replies
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
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?
