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

 

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.