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 Team,
I have two tables, Sales and Currency. See below:
I would like to populate Sales.Exchange Rate GBP and Sales.Exchange Rate USD with the exchange rates from the currency table (values above are my expected results)
The logic I need to use looks something like this (For GBP):
select earliest exchange rate from Currency where Sales.Currency = Currency.Source and Currency.Target = 'GBP' and Sales.Date > Currency.Date
How can I accomplish this?
Solved! Go to Solution.
https://www.dropbox.com/s/o04lz90nbzttotv/reporting%20model.pbix?dl=0
Exchange rate GBP CC=
VAR sourcecurrency = Requests[Currency]
VAR targetcurrency = "GBP"
VAR currentdate = Requests[Date Requested]
VAR maxcurrencydate =
CALCULATE (
MAX ( 'Currency'[date] ),
FILTER (
'Currency',
'Currency'[date] <= currentdate
&& 'Currency'[sourcecurrency] = sourcecurrency
&& 'Currency'[targetcurrency] = targetcurrency
)
)
RETURN
CALCULATE (
CALCULATE ( SUM ( 'Currency'[exchangerate] ) ),
FILTER (
'Currency',
'Currency'[date] = maxcurrencydate
&& 'Currency'[sourcecurrency] = sourcecurrency
&& 'Currency'[targetcurrency] = targetcurrency
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you @Jihwan_Kim ,
I appreciate the time you spent doing this, I will try and make it work
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I see you have created a SourceCurrency table, is this a necessary step in addition to my current currency table? My source currencies have more than just AUD so ideally I would rather not have to create this
J
You are right.
If you do not have other currencies, it is not mandatorily needed.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim
I have tried to implement your solution but it is not working, I have attached a link to a PowerBI model. As you can see I am trying to add a new column to my table 'Requests' but it is returning no results
https://www.dropbox.com/s/o04lz90nbzttotv/reporting%20model.pbix?dl=0
Exchange rate GBP CC=
VAR sourcecurrency = Requests[Currency]
VAR targetcurrency = "GBP"
VAR currentdate = Requests[Date Requested]
VAR maxcurrencydate =
CALCULATE (
MAX ( 'Currency'[date] ),
FILTER (
'Currency',
'Currency'[date] <= currentdate
&& 'Currency'[sourcecurrency] = sourcecurrency
&& 'Currency'[targetcurrency] = targetcurrency
)
)
RETURN
CALCULATE (
CALCULATE ( SUM ( 'Currency'[exchangerate] ) ),
FILTER (
'Currency',
'Currency'[date] = maxcurrencydate
&& 'Currency'[sourcecurrency] = sourcecurrency
&& 'Currency'[targetcurrency] = targetcurrency
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
Thank you, it works.
I had to add a DATEVALUE function to my currentdate variable as it was complaining about comparing date and strings.
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |