Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ) )
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |