Use currency table to calculate rate to third currency

[PBI-BOLA]

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. 

[DataInsights · Accepted solution]

@PBI-BOLA,

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

[PBI-BOLA]

Thank you very much!