3 Replies Latest reply on Oct 28, 2016 11:08 AM by Shawn Wallwork

    % change from previous fiscal year

    Vincent Baumel

      Hi friends! I'm trying to clean up an aspect of a dashboard I've inherited, and I was wondering if there's a better way to do it. I'll link a workbook showing how it currently is (translated into Superstore data)

       

      I'm looking at sales figures for each dimension sliced down to YEAR(Invoice Date). I've got a column for each year and each column shows both the net sales and the % change from the previous fiscal year. I'd like a simpler way to maintain this - my calculated field reads like this:

       

      IF MIN([Order Date]) < DATE("7/1/2015")

          THEN (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

          ELSE Null

      END

       

      Ideally I'd like a calc that doesn't require me to go in and manually change it every time the fiscal year turns over. The idea is that the % Change field populates once that fiscal year is complete. I'm also curious if there's a way to have this "previous fiscal year" be automatically sorted in descending sales amounts (so that I don't have to manually change it each fiscal year), but perhaps that should be a separate forum discussion. This worksheet was set up a couple years ago by someone I've never met, so any streamlining help would be appreciated! My fiscal year starts on July 1, which I think is what's throwing me off.