Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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 |
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.
Solved! Go to Solution.
Hi @SofieSwensson ,
Does your ExchangeRates table insert a new row of
LastDato | FromCurrency | Rate |
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
@SofieSwensson , see if these solutions can help
https://www.youtube.com/watch?v=4dosxfNxR6M
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1
https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-...
https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...
https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bi
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
LastDato | FromCurrency | Rate |
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |