Forum Discussion

hadjigeo's avatar
hadjigeo
Helper I
6 years ago
Solved

Exchange rate

Hi,

 

i have a column with values that has multiple currencies such as:

 

AED

USD

EUR

SAR

and i want to create a new column that will convert all of them in USD and one more that will convert all the data in EUR.

 

how is this possible?

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

     hi hadjigeo 

    If so, just adjust the formula to create a measure instead of column

    lastest rate = MAXX(TOPN(1,FILTER('Dim daily rates','Dim daily rates'[bpc_name]=MAX('Table'[bpc_name])),'Dim daily rates'[bpc_ExchangeDate],DESC),[bpc_ExchangeRate])

     

    Regards,

    Lin

6 Replies

    • hadjigeo's avatar
      hadjigeo
      Helper I
      6 years ago

      yes I have an exchange table with daily rates and currency. see below an example of the table

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi  hadjigeo 

        For your case, you could try this way as below:

        Step1:

        Add a bpc_name in your basic fact table, you could add it in edit queries or use this simple dax

        Column = 'Table'[Currency]&"-EUR"

        Step2:

        Add a lastest rate column in fact data table as below:

        lastest rate = MAXX(TOPN(1,FILTER('Dim daily rates','Dim daily rates'[bpc_name]='Table'[bpc_name]),'Dim daily rates'[bpc_ExchangeDate],DESC),[bpc_ExchangeRate])

         

        Now you could use this rates column in calculated column or measure to get your requriement.

         

        here is sample pbix file, please try it.

         

        Regards,

        Lin