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

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

    Nicola Prime

      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([2014]) - SUM([2013])) / SUM([2013])

       

      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
          Joe Oppelt

          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
            Nicola Prime

            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