4 Replies Latest reply on Jun 13, 2018 1:16 PM by Shinichiro Murakami

    Calculation between current and prior year

    Fadil B

      Hello,

      In my data I have year, department and price. I want the user to select a year and display only the lowest price within a given department. Then I want to calculate the difference between the minimum price for the selected year and the prior year.

      The problem I am having is the difference won’t calculate correctly based on the current and previous year. I have tried to create table calculations by going on specific dimensions and picking year but I keep getting the wrong calculations.

      Here is a formula of what I am trying to do

      Min(Price for finance from (2015)) – min(price for finance from (2014)) / min(price for finance from (2014))…This would show: 300-200 / 200 = 50%

       

      A sample of my data is below and a workbook is attached.

      Year

       

      Department

      Price

      % diff

      2014

       

      finance

      200

      2014

       

      finance

      100

      2014

       

      IT

      99

      2014

       

      IT

      80

      2014

       

      Marketing

      30

      2014

       

      Marketing

      70

      2015

       

      finance

      300

      2015

       

      finance

      400

      2015

       

      IT

      100

      2015

       

      IT

      300

      2015

       

      Marketing

      200

      2015

       

      Marketing

      400

      2016

       

      finance

      300

      2016

       

      finance

      200

      2016

       

      IT

      100

      2016

       

      IT

      50

      2016

       

      Marketing

      250

      2016

       

      Marketing

      150

       

       

       

      Thanks!