2 Replies Latest reply on Mar 15, 2018 10:59 AM by Jonathan Hodge

    Calculating Percentage of MTD compared to Previous MTD

    Jonathan Hodge

      Howdy All!

       

      I am trying to re-create tables similar to below. The following was run on Oct 6 2017, and compares to the previous month (Sept 2017, 1-6) and previous year (Oct 2016, 1-6).

       

       

      I luckily have all my date metrics figured out in my worksheet. My question is just how to I calculate the percentage between these sheets?

       

      Attached is an example I created using superstore data and my MTD and Prev MTD logic. I base it off of December 13, 2017.

       

       

      How do I compare these 2 Sales numbers together to derive my percentages? I'm confused on my if statement trying to make an aggregate of SUM based off of a boolean.

       

      Previous Month MTD DT

      DATETRUNC('month', [Order Date]) = DATETRUNC('month', DATEADD('month', -1, [Date]))

      AND DATETRUNC('day', [Order Date]) <=  DATETRUNC('day', DATEADD('month', -1, [Date]))

       

      Summing for Percentage Test? (ERROR)

      IF [Previous Month MTD DT] = TRUE

      THEN SUM([Sales])

      ELSE 0

      END

       

      Thanks!