Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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]
)
@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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
66 |