Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a table like the below
Currency_Code | Starting_Date | Monthly Average Conversion Rate to GBP |
AED | 01/11/2023 00:00 | 4.553310714 |
AED | 01/10/2023 00:00 | 4.469232258 |
AED | 01/09/2023 00:00 | 4.552056667 |
GBP | 01/11/2023 00:00 | 1 |
GBP | 01/10/2023 00:00 | 1 |
GBP | 01/09/2023 00:00 | 1 |
AUD | 01/11/2023 00:00 | 1.911332143 |
AUD | 01/10/2023 00:00 | 1.916648387 |
AUD | 01/09/2023 00:00 | 1.928933333 |
EUR | 01/11/2023 00:00 | 1.147792857 |
EUR | 01/10/2023 00:00 | 1.151916129 |
EUR | 01/09/2023 00:00 | 1.160366667 |
SGD | 01/11/2023 00:00 | 1.673132143 |
SGD | 01/10/2023 00:00 | 1.665458065 |
SGD | 01/09/2023 00:00 | 1.688866667 |
USD | 01/11/2023 00:00 | 1.239860714 |
USD | 01/10/2023 00:00 | 1.216941935 |
USD | 01/09/2023 00:00 | 1.23949 |
I'm trying without luck to create a calculated column that would take the monthly to gbp rate and divide it by the monthly rate for USD within the relevant month, trying to do this without the "Calculate" funtion as I know that can cause performance issues. This would allow me to get the direct conversion for each into USD.
My table has been simplified for this question. The actual table would contain rates for each day throughout a month.
Solved! Go to Solution.
Try this calculated column:
GBP/USD Rate =
VAR vStartingDate = 'Currency'[Starting_Date]
VAR vUSDRate =
MAXX (
FILTER (
'Currency',
'Currency'[Currency_Code] = "USD"
&& 'Currency'[Starting_Date] = vStartingDate
),
'Currency'[Monthly Average Conversion Rate to GBP]
)
VAR vResult =
DIVIDE ( 'Currency'[Monthly Average Conversion Rate to GBP], vUSDRate )
RETURN
vResult
Proud to be a Super User!
Try this calculated column:
GBP/USD Rate =
VAR vStartingDate = 'Currency'[Starting_Date]
VAR vUSDRate =
MAXX (
FILTER (
'Currency',
'Currency'[Currency_Code] = "USD"
&& 'Currency'[Starting_Date] = vStartingDate
),
'Currency'[Monthly Average Conversion Rate to GBP]
)
VAR vResult =
DIVIDE ( 'Currency'[Monthly Average Conversion Rate to GBP], vUSDRate )
RETURN
vResult
Proud to be a Super User!
Thank you very much!
User | Count |
---|---|
134 | |
72 | |
72 | |
55 | |
53 |
User | Count |
---|---|
202 | |
95 | |
63 | |
62 | |
53 |