Forum Discussion

Aleli's avatar
Aleli
Frequent Visitor
9 years ago

Dynamic currency conversion

Hi,   Can anyone suggest a simpler way to show converted amount to USD?   Currently, here's what I have: Table 1 - COUNTRY, YEAR, MONTH, INCOME (in local currency) Table 2 - YEAR, MONTH, LOCAL ...
Tips and Tricks
Tutorial Requests
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

Aleli

 

In this scenario, if these two tables are related to a full calendar date table. You can use SAMEPERIODLASTYEAR() function to get the last year exchange rate. You can create a measure in Table 2.

 

exchange rate last year = CALCULATE(AVERAGE(Table2[EXCHANGE RATE]),SAMEPERIODLASTYEAR(Date[Date]))

 

If you only have YEAR and MONTH column in both tables, you can create a calculated column to get the previous YEAR EXCHANGE RATE.

 

previous year exchange rate = 
CALCULATE(
AVERAGE(Table2[EXCHANGE RATE]),
FILTER(Table2, Table2[YEAR]=EARLIER(Table2[YEAR])-1 && Table2[MONTH] = EARLIER(Table2[MONTH]))
)

Regards,

 

  • Aleli's avatar
    Aleli
    Frequent Visitor
    9 years ago

    Hi v-sihou-msft,

     

    I copied your second formula but got an error saying "EARLIER refers to an ealier row context which doesn't exist".