Forum Discussion

jfournier's avatar
jfournier
Frequent Visitor
8 years ago
Solved

Find last value based on date

Hello,   I have 2 tables : Transactions, with datas like PAYMENT_DATE, CURRENCY, AMOUNT EXH_RATE, with datas like : CURRENCY, FROM DATE, EXCH_RATE   I want to find the latest Exchange Rate (i...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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]
    )
)
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

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's avatar
jfournier
Frequent Visitor
8 years ago

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 !