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,
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 ) )
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |