exchange rate changes, but only gets highest number.

[SofieSwensson]

Hi all. 
I have a problem with getting the right exchange rate, because it changes over time. Not everyday but almost once in a month. 
I have two tabels, some with the orders and another with exchange rate. See below: 

SalesOrderLines: 

SalesOrderNumber	LineAmount	Currency
SO20459	156,69	EUR
SO25378	29.800,71	EUR
SO30972	2.069,64	USD

ExchangeRates: 

LastDato	FromCurrency	Rate
31/07/2020	EUR	7,44
31/07/2020	USD	6,31
31/08/2020	EUR	7,45

In the SalesOrderLines Tabel i make a new column that is called Rate. There i want the newest exchange rate shown. 
But when i use the following statement, i get the biggest exchange rate - i think its the MAX statement, but i dont know what else to use. 

Rate =
CALCULATE( MAX(ExchangeRates[Rate]);
FILTER( ExchangeRates;
ExchangeRates[StartDate].[Date] <= Today();

Filter ( ExchangeRates; ExchangeRates[FromCurrency] = SalesOrderLines[CurrencyCode])
)

I cant check on a date in SalesOrderLines Table because its not allways filled out. Så I just want to check the date on ExchangeRates Tabel is "arround" today. 

Is there any who got a solution? 

Thx in advance. 

[v-alq-msft · Accepted solution]

Hi, @SofieSwensson 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

SalesOrderLines:

ExchangeRates:

You may create a calculated column as below.

NewRate = 
CALCULATE(
    MAX(ExchangeRates[Rate]),
    FILTER(
        ExchangeRates,
        [LastDate]=
        CALCULATE(
            MAX(ExchangeRates[LastDate]),
            FILTER(
                ExchangeRates,
                [LastDate]<=TODAY()
            )
        )&&
        [FromCurrency]=EARLIER(SalesOrderLines[Currency])
    )
)

Today is 9/21/2020. Here is the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@SofieSwensson , Try like

Rate =
CALCULATE( lastnonblankvalue(ExchangeRates[LastDato], max(ExchangeRates[Rate]));
FILTER( ExchangeRates;
ExchangeRates[StartDate].[Date] <= Today()) && ExchangeRates[FromCurrency] = SalesOrderLines[CurrencyCode])

but it is measure you need some changes

[SofieSwensson]

amitchandak , I get the error that Today returns a true/false false, and thats also true. 
So can i use something else to check up against the date?