Exchange rate

[hadjigeo]

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?

[amitchandak]

Do you have Exchange rate date and currency wise

[hadjigeo]

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

[v-lili6-msft]

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

[hadjigeo]

Hi @v-lili6-msft  for the step 2 when i create a table it doesnt let me to use the MAXX.

i Am using a directquery, does this has to do anything with that?

[v-lili6-msft · Accepted solution]

 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

[ZChase]

whatif exchange rates table is missing dates. Should i fix the exchange rate table to fill up the missing dates or use measure to calculate the value in USD currency with reference to native currency and transaction dates.