Find last value based on date

[jfournier]

Hello,

I have 2 tables :

1. Transactions, with datas like PAYMENT_DATE, CURRENCY, AMOUNT
2. EXH_RATE, with datas like : CURRENCY, FROM DATE, EXCH_RATE

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 :

1. 10.000 CNY 10/01/2017
2. 350.000 HUF 07/15/2017

EXCH_RATE:

1. CNY 09/10/2017 7.89
2. HUF 06/10/2017 310.67
3. HUF 07/10/2017 309.89
4. CNY 08/01/2017 7.85

This should return, for TRANSACTION :

1. 7.89
2. 309.89

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

[v-chuncz-msft · Accepted solution]

@jfournier,

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]
    )
)

[jfournier]

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 !

[Zubair_Muhammad]

@jfournier

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
        )
    )