cancel
Showing results for
Did you mean:
Helper I

## 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?

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User IV

Do you have Exchange rate date and currency wise

Proud to be a Super User!

Helper I

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

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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?

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

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.

Announcements