Forum Discussion
Convert currency
I'm trying to convert CAD to USD using month end exchange rate. Below are the tables I am using. How do I use the "rate" (this is a month end rate) in the Echange Rate Table (below) to convert the "amount" column in the bavProjectDtl MMI table (also below)? I was thinking I could use the month name as a pivot point bewteen to the two tables? I tried to use the LOOKUPVALUE fuction in DAX with little success, but I don't know much about that function...Maybe there is a way to drop the exchange rate from the Exchange Rate table into the BavProjectDtl MMI table? If so, I could do a simple multiplication column in that table...
Any thoughts would be appreciated!!!
Hi kglende,
If you have created a one to many relationship betwee Echange Rate Table and bavProjectDtl MMI table, you could directly point to the Rate column in bavProjectDtl MMI table.
Create a calculated column using this formula:
Rate=Related('Echange Rate'[Rate])If there isn't any relationship between these two tables, you could use LOOKUPVALUE function to create calculated column:
Rate=LOOKUPVALUE('Echange Rate'[Rate],'Echange Rate'[Month],'bavProjectDtl MMI'[Month Name])Best regards,
Yuliana Gu
1 Reply
v-yulgu-msftMicrosoft Employee
Hi kglende,
If you have created a one to many relationship betwee Echange Rate Table and bavProjectDtl MMI table, you could directly point to the Rate column in bavProjectDtl MMI table.
Create a calculated column using this formula:
Rate=Related('Echange Rate'[Rate])If there isn't any relationship between these two tables, you could use LOOKUPVALUE function to create calculated column:
Rate=LOOKUPVALUE('Echange Rate'[Rate],'Echange Rate'[Month],'bavProjectDtl MMI'[Month Name])Best regards,
Yuliana Gu