Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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]
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |