Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |