Convert currency

[GraceTCL]

Hi I need to convert the local currency to reporting currency.

In the exchange rate dimension table, the date reference is based on start or end date of the month while my fact table is based on calendar date. This is how I have set up my tables. Can anyone advise how can I go about converting the currency using dax measures if possible?

[Jihwan_Kim]

Hi, @GraceTCL 

I am not sure if I understood your question correctly, but I just assumed and created a sample pbix file like below.

Please check the below picture and the link down below, whether it suits your case.

all measures are in the sample pbix file.

https://www.dropbox.com/s/5ewsg6icb0hazdb/grace.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[GraceTCL]

@Jihwan_Kim Thanks for your fast response. Unfortunately it doesnt work.

Here's more details on how the table are linked by following the yellow highlight.

The outcome that I had by following your advice. Still null.

[Jihwan_Kim]

Hi, @GraceTCL 

Thank you for your feedback.

In my opinion, your model and my model are totally different. I think my measure cannot be effectively used in your model.

And I am not sure about your date_id column and relationships between tables.

Please share your sample pbix file's link here, then I can try to come up with a more accurate measure, and I also want to suggest changing a relationship.

Thanks.

[GraceTCL]

hi @Jihwan_Kim 

I have put in some sample data in an excel but am not sure how to create a sample pbix file fast enough. Hope this is sufficient for you to understand and help. Thank you 🙂

https://www.dropbox.com/scl/fi/4a7kp6rmpahff4l221qx7/Query-currency.xlsx?dl=0&rlkey=ckkt7f3voecuvmto... 

[Jihwan_Kim · Accepted solution]

Hi, @GraceTCL 

Thank you for sharing the data.

- sorry to say that I could not use the date-id column effectively because my computer generally uses mm/dd/yyyy format. And, it is quite easy for me to create an effective measure when using the actual date column, instead of using date_id column in number format.

- I disconnected one relationship. please check the below picture and the sample pbix file's link down below.

Renue Exchange rate apply =

SUMX (

VALUES ( 'Calendar'[Date] ),

CALCULATE (

SUMX (

'Food Metrics',

'Food Metrics'[Revenue_LC]

* CALCULATE (

SELECTEDVALUE ( 'Exchange rate'[Exchange rate] ),

FILTER (

'Exchange rate',

MIN ( 'Calendar'[Date] ) >= 'Exchange rate'[Start Date]

&& MAX ( 'Calendar'[Date] ) <= 'Exchange rate'[End Date]

)

)

)

)

)

https://www.dropbox.com/s/8opob2hwq3razo5/gracetcl.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Greg_Deckler]

@GraceTCL - Sample data would help as it really depends on your data setup. But if I understand this correctly, you are probably going to want to do one of two things. Either make your relationship between the exchange rate table and food metrics table Both or use LOOKUPVALUE or something like MAXX(FILTER(...)...) to lookup your exchange rate for a particular day. This might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430