Forum Discussion

samerhaidar0811's avatar
samerhaidar0811
Frequent Visitor
3 years ago
Solved

Monthly Currency Exchange

Hi guys,

 

I hope you are having a great week.

 

Please consider the fact table below. It contains the cost and the currency in which the cost is displayed.

Year  Month Currency Cost 
20221ZAR20000
20222ZAR20022
20223ZAR22000
20224ZAR30000
20221USD10203
20222USD34323
20223USD32453
20224USD46563
20221TRY50434
20222TRY43333
20223TRY43433
20224TRY54234


I also have a currency table containing the monthly and yearly currency exchange rates.

Year  Month  Currency  to GBP
20221ZAR22
20222ZAR23
20223ZAR23
20224ZAR25
20221USD12
20222USD12
20223USD13
20224USD14
20221TRY111
20222TRY123
20223TRY124
20224TRY124


I want to create a measure that would convert my cost column to cost in GBP (British Pounds) based on the exchange rates in the the currency table.


Any leads on how to do that?


Need Help
Show and Tell
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    My fault, I forgot to include the currency code in the lookup

    Cost in GBP =
VAR CurrentMonth = 'Table'[Month]
VAR CurrentYear = 'Table'[Year]
VAR CurrentCurrency = 'Table'[Currency]
VAR ExchRate =
    LOOKUPVALUE (
        'Currency'[to GBP],
        'Currency'[Year], CurrentYear,
        'Currency'[Month], CurrentMonth,
        'Currency'[Currency], CurrentCurrency
    )
RETURN
    'Table'[Cost] * ExchRate

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could add a calculated column to the fact table like

    Cost in GBP =
VAR CurrentMonth = 'Table'[Month]
VAR CurrentYear = 'Table'[Year]
VAR ExchRate =
    LOOKUPVALUE (
        'Currency'[to GBP],
        'Currency'[Year], CurrentYear,
        'Currency'[Month], CurrentMonth
    )
RETURN
    'Table'[Cost] * ExchRate
    • samerhaidar0811's avatar
      samerhaidar0811
      Frequent Visitor
      3 years ago

      Hi,

       

      Im getting the below error when i create the column. 

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        My fault, I forgot to include the currency code in the lookup

        Cost in GBP =
VAR CurrentMonth = 'Table'[Month]
VAR CurrentYear = 'Table'[Year]
VAR CurrentCurrency = 'Table'[Currency]
VAR ExchRate =
    LOOKUPVALUE (
        'Currency'[to GBP],
        'Currency'[Year], CurrentYear,
        'Currency'[Month], CurrentMonth,
        'Currency'[Currency], CurrentCurrency
    )
RETURN
    'Table'[Cost] * ExchRate