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

Tips and Tricks
Tutorial Requests

2 Replies

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