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!