2 Replies Latest reply on May 31, 2016 1:36 PM by katie.gilderman

# Foreign Exchange Effect on Margin

I am looking to calculate how much of our year over year margin increase/decrease was due to foreign exchange rates changing.  I have the margins and FX rates by month for multiple countries.  The methodology that I want to use is below:

(LYTD Margin / June 2016 FX Rate) - (LYTD Margin / June 2015 FX Rate) = FX Change

I am looking to use the June FX rates as my fixed values because that is when my fiscal year starts.

I cannot get this equation to work within tableau.

Below are my equations.  I know the error is somewhere within the June Last Year FX and June This Year FX equations because the Margin portion works perfectly for many other equations.

June Last Year FX

IF MONTH([Ship Date - Actual]) = 6 AND YEAR([Ship Date - Actual]) = 2014 THEN

ZN({ FIXED [Global Country],[Fiscal Year],[Calendar Month]:AVG([FX])})

ELSE 0

END

June This Year FX

IF MONTH([Ship Date - Actual]) = 6 AND YEAR([Ship Date - Actual]) = 2015 THEN

ZN({ FIXED [Global Country],[Fiscal Year],[Calendar Month]:AVG([FX])})

ELSE 0

END

Total FX Effect

(SUM([Last Fiscal Year IOMM])/MAX([June This Year FX ])) - (SUM([Last Fiscal Year IOMM])/MAX([June Last Year FX]))

By using the methodology above my calculation for one of the countries should be:

(\$3,544,437 / 27.2) - (\$3,544,437 / 25.9) = - \$6,540.67

Tableau is calcuating - \$172,916

Any help is appreciated. Thanks!

• ###### 1. Re: Foreign Exchange Effect on Margin

You've provided a great level of detail, that's super helpful. However in order to really help, we'll need a Tableau Packaged Workbook to go along with this so we can truly replicate.

Thanks!

• ###### 2. Re: Foreign Exchange Effect on Margin

Hi Tom,

Thanks for the reply.  Attached is a .twbx with some example info.  Below is an example of what I think I should get based on the methodology above.  Any help is appreciated.

Thanks!