Dynamic currency conversion

[Aleli]

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 CURRENCY, EXCHANGE RATE (to convert local currency to USD)

Year Slicer from 2013 to 2017 -- Whatever is selected will be treated as "CURRENT YEAR" (CY), and will be compared against the "PRIOR YEAR" (PY = CY-1) income.

My goals:

Present CY and PY figures in a bar chart. X-axis=COUNTRY, Y-axis=INCOME

Add a new slicer that will give the user an option whether to use current year or prior year exchange rate

I believe only the CY figure should change, depending on the exchange rate year slicer.

Many thanks!

[v-sihou-msft]

@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]

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".