Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jfournier
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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]
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@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]
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.