cancel
Showing results for
Did you mean:
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Convert currency

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

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

## Re: Convert currency

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

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

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors