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):
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:
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.
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?