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

    Foreign Exchange Effect on Margin

    katie.gilderman

      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!