3 Replies Latest reply on Feb 13, 2017 9:46 AM by Jason Bernstein

    Year to Year Inflation of Common Products Across Year

    Jason Bernstein

      I would like to change my current methodology on trend calculations.  Currently, the methodology is as written:

       

      ( SUM( IIF( YEAR( [Date of Fill] ) = 2016  ,  [Cost]  ,  0 ) )

      /

      SUM( IIF( YEAR( [Date of Fill] ) = 2016  ,  [Quantity]  ,  0 ) ) )

      /

      ( SUM( IIF( YEAR( [Date of Fill] ) = 2015  ,  [Cost]  ,  0 ) )

      /

      SUM( IIF( YEAR( [Date of Fill] ) = 2015  ,  [Quantity]  ,  0 ) ) ) - 1

       

       

      This is not however how many would define "Inflation."  This is more of an overall cost increase.

      What I need to do instead is find the products that existed in both years, 2016 and 2015, and calculate the inflation on ONLY those products.

       

      In code that would look something like this:

      ( SUM( IIF( YEAR( [Date of Fill] ) = 2016 AND <product exists in 2015>  ,  [Cost]  ,  0 ) )

      /

      SUM( IIF( YEAR( [Date of Fill] ) = 2016 AND <product exists in 2015>  ,  [Quantity]  ,  0 ) ) )

      /

      ( SUM( IIF( YEAR( [Date of Fill] ) = 2015 AND <product exists in 2016>  ,  [Cost]  ,  0 ) )

      /

      SUM( IIF( YEAR( [Date of Fill] ) = 2015 AND <product exists in 2016>  ,  [Quantity]  ,  0 ) ) )  -  1

       

       

      Any solution would be greatly appreciated, thanks!