Forum Discussion

kglende's avatar
kglende
Regular Visitor
9 years ago
Solved

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 "a...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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