The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I have 2 tables :
I want to find the latest Exchange Rate (ie the most recent) for my Transaction Currency and date in the EXCH_RATE table.
for example :
Transaction :
EXCH_RATE:
This should return, for TRANSACTION :
I tried several solutions (Calculate with filter, MAX, ...) But I can't manage to make it Work.
The last thing I tried :
TxChange = IF(TRANSACTION[CURRENCY] = "EUR";1;CALCULATE(MAX(EXCH_RATE[EXCH_RATE]);FILTER(EXCH_RATE;EXCH_RATE[DATE_FROM]=EARLIER(TRANSACTION[PAYMENT_DATE]));FILTER(EXCH_RATE;EXCH_RATE[CURRENCY] = TRANSACTION[CURRENCY])))
Thanks a lot for your help !
Julien
Solved! Go to Solution.
You may refer to the following DAX that adds a calculated column.
Column = IF ( 'TRANSACTION'[CURRENCY] = "EUR", 1, MAXX ( TOPN ( 1, FILTER ( EXCH_RATE, EXCH_RATE[CURRENCY] = 'TRANSACTION'[CURRENCY] && EXCH_RATE[DATE_FROM] <= 'TRANSACTION'[PAYMENT_DATE] ), EXCH_RATE[DATE_FROM], DESC ), EXCH_RATE[EXCH_RATE] ) )
You may refer to the following DAX that adds a calculated column.
Column = IF ( 'TRANSACTION'[CURRENCY] = "EUR", 1, MAXX ( TOPN ( 1, FILTER ( EXCH_RATE, EXCH_RATE[CURRENCY] = 'TRANSACTION'[CURRENCY] && EXCH_RATE[DATE_FROM] <= 'TRANSACTION'[PAYMENT_DATE] ), EXCH_RATE[DATE_FROM], DESC ), EXCH_RATE[EXCH_RATE] ) )
Hello,
Thanks for your replies.
I manages to do something with this expression :
TxChange = IF(TRANSACTION[CURRENCY] = "EUR";1;1/CALCULATE(SUM(EXCH_RATES[EXCH_RATE]);FILTER(EXCH_RATE;EXCH_RATES[DATE_FROM] = MAX(EXCH_RATES[DATE_FROM]));FILTER(EXCH_RATES;EXCH_RATES[CURRENCY]=TRANSACTION[CURRENCY])))
I don't know if it's really OK but I checked a dozen of lines and it's OK.
I didn't know TOPN Function ! It's exactly what I need for a lot of operations !!
Thanks a lot !
Try this calculated column in Transactions Table
= VAR LatestDate = CALCULATE ( MAX ( EXCH_RATE[DATE_FROM] ), FILTER ( EXCH_RATE, EXCH_RATE[CURRENCY] = 'TRANSACTION'[CURRENCY] ) ) RETURN CALCULATE ( VALUES ( EXCH_RATE[EXCH_RATE] ), FILTER ( EXCH_RATE, EXCH_RATE[CURRENCY] = 'TRANSACTION'[CURRENCY] && EXCH_RATE[DATE_FROM] = LatestDate ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
142 | |
109 | |
107 | |
76 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |