Measure to convert currency

[Br1-981]

Hi,

I have a fact table:

where sales values are in local currency and I need to convert into GBP using a custom table monthly based:

I Would create a measure that for each combination of country/year/month gives me the result of sales*exchange rate

I created a calculated column in DAX:

Cost in EUR = 
VAR CurrentMonth = Fact[MONTH]
VAR CurrentYear = Fact[YEAR]
VAR CurrentCountry = Fact[COUNTRY]
VAR ExchRate =
    LOOKUPVALUE (
        'ExRate'[EXCHANGE RATE TO GBP],
        'ExRate'[YEAR], CurrentYear,
        'ExRate'[MONTH], CurrentMonth,
        'ExRate'[COUNTRY], CurrentCountry
    )
RETURN
    Fact[SALES  LOCAL CURRENCY] * ExchRate

but I'm worried about performances

Thanks

[amustafa]

See my solution in this post:

Re: Exchange Rate Conversion with Date Selection (... - Microsoft Fabric Community

[Br1-981 · Accepted solution]

Thanks, starting from your approach I developed a new measure:

SPEND_EUR = 
VAR CurrentMonth = MONTH(Data[Date])
VAR CurrentYear = YEAR(Data[Date])
VAR CurrentCountry = Data[Country_Code]
VAR ExchRate =
    LOOKUPVALUE (
        'ExRate'[Exchange Rates],
        'ExRate'[Year], CurrentYear,
        'ExRate'[Month], CurrentMonth,
        'ExRate'[Country_Code], CurrentCountry
    )
RETURN
    WeeklyData[SPEND] * ExchRate