10 Replies Latest reply on Jun 28, 2018 4:43 PM by Shinichiro Murakami

# Referencing exchange rates in different years

Hi everyone,

I'm trying to calculate sales in USD from local currency.  The issue I'm running into is 1 country happens to have 2 exchange rates

Here's an example of the rates:

 Bulgaria EUR 2015 0.78 Bulgaria EUR 2016 0.91 Bulgaria EUR 2017 0.92 Bulgaria EUR 2018 0.86 Bulgaria BGN 2015 1.5359 Bulgaria BGN 2016 1.77 Bulgaria BGN 2017 1.793 Bulgaria BGN 2018 1.6787

The issue is that all sales from 2015-2017 are in EUR and all sales in 2018 are in BGN.    I want to be able to look at the 2017 sales (EUR) in the 2018 exchange rate (BGN).  If I select these parameters, nothing comes up.   I need to find a way to have it recognize the sales in 2017 are in EUR and use the 2018 EUR exchange rate for the conversion.

If anyone has any ideas, I'd greatly appreciate it!

• ###### 1. Re: Referencing exchange rates in different years

HI Jon,

Hope this helps.

Overview: Level of Detail Expressions

Thanks,

Shin

• ###### 2. Re: Referencing exchange rates in different years

How would I do that if the year is a date?  Right now I get the error that I can't compare date and integer values.

• ###### 3. Re: Referencing exchange rates in different years

Try this.

• ###### 4. Re: Referencing exchange rates in different years

I think we're close.  I have many other countries to consider.  Is there a way to apply this to only Bulgaria?

• ###### 5. Re: Referencing exchange rates in different years

IF  YEAR([Year])<=2017

AND

[Country] = "BULGARIA"

THEN

"EUR"

ELSE

[Code]

END

I think this is working, but not completely sure

• ###### 7. Re: Referencing exchange rates in different years

That worked.  Now the issue is that my sales are being multiplied by 4 (4 years worth of sales)

• ###### 8. Re: Referencing exchange rates in different years

Please attach twbx and clarify the expected result.

I am getting tired of working without data.

Thanks,

Shin

• ###### 9. Re: Referencing exchange rates in different years

Here's a rough version of what I'm working with.  I want to be able to adjust the Financial Year and Exchange Rate Year independently and have the correct exchange rate get applied to the USD conversion calculation.

• ###### 10. Re: Referencing exchange rates in different years

Hi Jon

Not exactly sure glad if this helps.

Thanks,

Shin