2 Replies Latest reply on Apr 7, 2017 1:24 AM by SANDIP SHARMA

    How to compute percent change field for values that are computed using conditional logic around a date parameter

    Michael Polino

      Hi all-

       

      In the attached worksheet, I've set a date parameter that allows the user to focus on a specific week.  Rather than filter the sheet, I calculate each field relative to the date parameter.  My cost field for the current week is calculated as IF [Date]=[Week Select] THEN [Cost] END and the cost field for the previous week is calculated as IF [Date]=[Week Select] - 7 THEN [Cost] END, etc...

       

      After calculating a rate field for the current and previous weeks, I'd like to show the percent change in rate week over week.  It seems like I can't calculate a field based on conditional logic that points to two different dates.

       

      The entries in my data don't appear consistently each week, so a LOOKUP() may not work.  In the table calc menu, the option for computing across the table isn't available.


      I'd like to keep the date parameter functionality, but I'm willing to change the format of my data if necessary.


      Thank you.