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

# Calculating Percentage of MTD compared to Previous MTD

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!

• ###### 1. Re: Calculating Percentage of MTD compared to Previous MTD

Hi Jonathan,

What percentage are you trying to calculate.  For example the % gain in September 2017 is relative to what?  Previous month, parameter month, previous year?

• ###### 2. Re: Calculating Percentage of MTD compared to Previous MTD

For this example I'd like to compare my snapshots of whats in the workbook. Can we find the percentage difference from current month MTD of sales compared to previous month MTD of sales?

44,098/53,981

That is 81%. I guess the "% Gained" should be -19%. So -19% is what I am looking for. But I could get that from finding the 81% as well.