Forum Discussion

Br1-981's avatar
Br1-981
Icon for Helper I rankHelper I
2 years ago
Solved

Measure to convert currency

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

 

 

Need Help
  • Br1-981's avatar
    Br1-981
    2 years ago

    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

2 Replies

    • Br1-981's avatar
      Br1-981
      Icon for Helper I rankHelper I
      2 years ago

      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