cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hadjigeo
Helper I
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

 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

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.

View solution in original post

6 REPLIES 6
amitchandak
Super User IV
Super User IV

Do you have Exchange rate date and currency wise



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Capture.PNG

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

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.

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?

 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

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.

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors