2 Replies Latest reply on Dec 16, 2016 12:54 AM by Nicola Prime

# Creating variance column between 'Current Month' and the Previous December

Hi All,

I am trying to create a variance column which shows the variances between the "current month" which is selected through a date parameter, and the previous December.

I am displaying the data as "Current month" plus the 12 months prior to this date

As I want the end user to be able to select any month they wish, using the following way of creating variances does not work for me as I would have to create a calculation for every possible month they may select:

• `IF YEAR([Order Date]) = 2013 THEN [Sales] ELSE 0 END`
• `IF YEAR([Order Date]) = 2014 THEN [Sales] ELSE 0 END`
• `(SUM() - SUM()) / SUM()`

Any suggestions on how I go about this?

Additionally, ideally I would like the variance column to include the selected current month name in the title -  e.g. if November is selected, the variance should say "Nov'16 vs Stnd Cost (Dec'15)", is this possible?

Thanks

Nicola

• ###### 1. Re: Creating variance column between 'Current Month' and the Previous December

Work it as numbers.  Your parameter can be an integer parameter with values of 1 through 12.  The DISPLAY value for each can be "January", "February", etc., so the user sees the names of the months, but the value of the parameter will be 1-12.

I assume you are also having the user select a year, or you have some way of knowing what year you are looking at,

I would have a calc that says (in pseudo-code here):

calc1:

If (this row is for the month and year we need to compare to December) THEN [Sales] END

That will capture all the data for the month and year you're concerned about.

Then I would have another calc:

Calc2:

if (this row is for December of the prior year) THEN [Sales] END

Those calcs will capture the data for the two subject months.  All other rows will be null.

Now SUM([Calc1]) will be the sum of sales for your subject month, and SUM([Calc2]) will be the sales for the previous December.  The math from there is basis stuff.

SUM([Calc1]) - SUM([Calc2]) is the difference between current month and last December, for example.

• ###### 2. Re: Creating variance column between 'Current Month' and the Previous December

Hi Joe,

1) Yes actually I already have my parameter displayed like this. However, when I created comparisons for another report I was working on, I followed the example I gave above to create the variance columns - the problem is, they do not adopt the name of the month they relate to - because it is dynamic I can't name them but I was wondering if there is a formula I can include in the title so that it displays the month name rather than for example "Date Control -1" 2) If I adopt your above suggestion, then doesn't that mean my other months will disappear? I am not quite clear on how I will present this in a formula.

3) I did the below using the Superstore data and when I add in the variance calculation, it distorts all the rows...and puts the variance calc on a different row which is not what I want to display!

I also want the variance column to sit in between Nov and Dec...is the only way to do this, to create a calculation for sales of every month I want to show so that I can order it how I wish using the measure names? So essentially I will follow my example above and repeat it 12 times for the twelve months I want to display - in the formula I will use my control date parameter as the base.

I thought there may be an easier/cleaner way in which to do this?  4) Lastly, any idea on how I change the colour just for the variance column?

Thanks

Nicola