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]
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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