Forum Discussion

jfournier's avatar
jfournier
Frequent Visitor
8 years ago
Solved

Find last value based on date

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

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community 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
        )
    )
  • 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 !