cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculate currency - With rates that changes once in a while

Hi All. 
I have a problem whit converting my SalesLines LineAmount to DKK. 
The problem is that the currency rates are placed in another tabel, and changes over time. Not every day, but once in a while. 
The data comes from Dynamics365 F&O, and i will post some of the data below. 

SalesOrderLines: 

SalesOrderNumberLineAmountCurrency
SO20459156,69EUR
SO2537829.800,71EUR
SO309722.069,64

USD

 

ExchangeRates: 

LastDatoFromCurrencyRate
31/07/2020EUR7,44
31/07/2020USD

6,31

 

What i want is: 
To have a new columm on SalesOrderLines, where it calculates the LineAmount to DKK, using the Rate from ExchangeRates. 
The rates changes over time, so i would like to use the newest every time. 

I have tried to following, but gets multible variables: 
LineAmountMST = SUMX('SalesOrderLines'; 'SalesOrderLines'[LineAmount] / LOOKUPVALUE(ExchangeRates[Rate]; ExchangeRates[Date]; SalesOrderLines[ConfirmedReceiptDate]; ExchangeRates[FromCurrency]; SalesOrderLines[CurrencyCode]))

Can any of you guys tell me how to do it? 🙂 

Thx. In Advance.

1 ACCEPTED SOLUTION

Hi @SofieSwensson ,

 

Does your ExchangeRates table insert a new row of 

LastDatoFromCurrencyRate

when there is an exchange rate change? And sort you ExchangeRates table by date descending.

 

Please use the following measure:

 

 

LineAmountMST =
SUMX (
    'SalesOrderLines',
    'SalesOrderLines'[LineAmount]
        / CALCULATE (
            FIRSTNONBLANK ( ExchangeRates[Rate], 1 ),
            FILTER (
                ALL ( 'ExchangeRates' ),
                ExchangeRates[FromCurrency] = 'SalesOrderLines'[CurrencyCode]
            )
        )
)

 

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

3 REPLIES 3

Hi Amitchandak
It dosent solve it, because they use a date, and i just want it to look at the newest date. 
Im not curtian what to do, do solve this.

Hi @SofieSwensson ,

 

Does your ExchangeRates table insert a new row of 

LastDatoFromCurrencyRate

when there is an exchange rate change? And sort you ExchangeRates table by date descending.

 

Please use the following measure:

 

 

LineAmountMST =
SUMX (
    'SalesOrderLines',
    'SalesOrderLines'[LineAmount]
        / CALCULATE (
            FIRSTNONBLANK ( ExchangeRates[Rate], 1 ),
            FILTER (
                ALL ( 'ExchangeRates' ),
                ExchangeRates[FromCurrency] = 'SalesOrderLines'[CurrencyCode]
            )
        )
)

 

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors