Compare holiday periods (e.g. Presidents Day +/- 10 days)

[RichardRayJH]

I want to compare a whole bunch of business metrics for several years for the days from 10 days before Presidents Day to 10 days after. I've got a method that gives me the data, but I cannot figure out a way to get the chart I need. I have to wonder if this wheel hasn't already been invented, so if someone has a reference I can look at, fire away!

Here's how I'm doing it now:

I've got the data in one form using a tally table from -10 to 10 and a table of the date of Presidents Day for the last 6 years. Doing a SQL cross join gives me the dates for each year that are -10 to 10 days off, and joining that to the business data gives me the numbers I need.

The hierarchy is

OffsetDay [-10...10]
Season [2007-2008 Winter ... 2012-2013 Winter]
LineOfBusiness [Lift Access, Mountain Sports School, Retail/Rental, Dining]

Measures are NetRevenue, SkierDays, TotalYield. NetRevenue is calculated by LOB and SkierDays are normally only reported for the Lift Access LOB, but I was pointed towards getting what I needed to calculate the TotalYield properly.

Based on all that I can do the data perfectly. It's dead on. Now, of course, they want to see it in a chart, and I can't figure out how to get it represented correctly.


The data comes out looking like this (reduced to just -2 ... 2 days, 2010-2011 and 2011-2012 seasons to make it a little more compact):

[TABLE="width: 420"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Offset, Seasons, Dates[/TD]
[TD]NetRevenue[/TD]
[TD]SkierDays[/TD]
[TD]TotalYield[/TD]
[/TR]
[TR]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-2011 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/19/2011[/TD]
[TD="align: right"]$1,472,219[/TD]
[TD="align: right"]16,230[/TD]
[TD="align: right"]$90.71[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$737,172[/TD]
[TD="align: right"]16,230[/TD]
[TD="align: right"]$45.42[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$373,132[/TD]
[TD][/TD]
[TD="align: right"]$22.99[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$236,718[/TD]
[TD][/TD]
[TD="align: right"]$14.59[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$125,197[/TD]
[TD][/TD]
[TD="align: right"]$7.71[/TD]
[/TR]
[TR]
[TD]2011-2012 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/18/2012[/TD]
[TD="align: right"]$1,767,422[/TD]
[TD="align: right"]18,087[/TD]
[TD="align: right"]$97.72[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$809,433[/TD]
[TD="align: right"]18,087[/TD]
[TD="align: right"]$44.75[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$433,780[/TD]
[TD][/TD]
[TD="align: right"]$23.98[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$379,840[/TD]
[TD][/TD]
[TD="align: right"]$21.00[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$144,369[/TD]
[TD][/TD]
[TD="align: right"]$7.98[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-2011 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/20/2011[/TD]
[TD="align: right"]$1,690,021[/TD]
[TD="align: right"]20,769[/TD]
[TD="align: right"]$81.37[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$844,597[/TD]
[TD="align: right"]20,769[/TD]
[TD="align: right"]$40.67[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$463,442[/TD]
[TD][/TD]
[TD="align: right"]$22.31[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$259,408[/TD]
[TD][/TD]
[TD="align: right"]$12.49[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$122,574[/TD]
[TD][/TD]
[TD="align: right"]$5.90[/TD]
[/TR]
[TR]
[TD]2011-2012 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/19/2012[/TD]
[TD="align: right"]$2,119,419[/TD]
[TD="align: right"]21,411[/TD]
[TD="align: right"]$98.99[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$972,360[/TD]
[TD="align: right"]21,411[/TD]
[TD="align: right"]$45.41[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$583,322[/TD]
[TD][/TD]
[TD="align: right"]$27.24[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$400,722[/TD]
[TD][/TD]
[TD="align: right"]$18.72[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$163,014[/TD]
[TD][/TD]
[TD="align: right"]$7.61[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-2011 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/21/2011[/TD]
[TD="align: right"]$1,504,784[/TD]
[TD="align: right"]18,246[/TD]
[TD="align: right"]$82.47[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$778,885[/TD]
[TD="align: right"]18,246[/TD]
[TD="align: right"]$42.69[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$444,891[/TD]
[TD][/TD]
[TD="align: right"]$24.38[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$180,060[/TD]
[TD][/TD]
[TD="align: right"]$9.87[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$100,948[/TD]
[TD][/TD]
[TD="align: right"]$5.53[/TD]
[/TR]
[TR]
[TD]2011-2012 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/20/2012[/TD]
[TD="align: right"]$1,761,686[/TD]
[TD="align: right"]15,957[/TD]
[TD="align: right"]$110.40[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$821,920[/TD]
[TD="align: right"]15,957[/TD]
[TD="align: right"]$51.51[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$513,234[/TD]
[TD][/TD]
[TD="align: right"]$32.16[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$331,284[/TD]
[TD][/TD]
[TD="align: right"]$20.76[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$95,248[/TD]
[TD][/TD]
[TD="align: right"]$5.97[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-2011 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/22/2011[/TD]
[TD="align: right"]$1,263,576[/TD]
[TD="align: right"]14,343[/TD]
[TD="align: right"]$88.10[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$684,599[/TD]
[TD="align: right"]14,343[/TD]
[TD="align: right"]$47.73[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$360,385[/TD]
[TD][/TD]
[TD="align: right"]$25.13[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$145,961[/TD]
[TD][/TD]
[TD="align: right"]$10.18[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$72,631[/TD]
[TD][/TD]
[TD="align: right"]$5.06[/TD]
[/TR]
[TR]
[TD]2011-2012 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/21/2012[/TD]
[TD="align: right"]$1,492,956[/TD]
[TD="align: right"]16,080[/TD]
[TD="align: right"]$92.85[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$754,294[/TD]
[TD="align: right"]16,080[/TD]
[TD="align: right"]$46.91[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$429,252[/TD]
[TD][/TD]
[TD="align: right"]$26.69[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$222,946[/TD]
[TD][/TD]
[TD="align: right"]$13.86[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$86,463[/TD]
[TD][/TD]
[TD="align: right"]$5.38[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-2011 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/23/2011[/TD]
[TD="align: right"]$1,167,401[/TD]
[TD="align: right"]13,773[/TD]
[TD="align: right"]$84.76[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$642,308[/TD]
[TD="align: right"]13,773[/TD]
[TD="align: right"]$46.64[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$327,113[/TD]
[TD][/TD]
[TD="align: right"]$23.75[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$123,568[/TD]
[TD][/TD]
[TD="align: right"]$8.97[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$74,412[/TD]
[TD][/TD]
[TD="align: right"]$5.40[/TD]
[/TR]
[TR]
[TD]2011-2012 Winter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/22/2012[/TD]
[TD="align: right"]$1,345,662[/TD]
[TD="align: right"]13,599[/TD]
[TD="align: right"]$98.95[/TD]
[/TR]
[TR]
[TD]Lift Access[/TD]
[TD="align: right"]$710,007[/TD]
[TD="align: right"]13,599[/TD]
[TD="align: right"]$52.21[/TD]
[/TR]
[TR]
[TD]Mountain Sports School[/TD]
[TD="align: right"]$350,793[/TD]
[TD][/TD]
[TD="align: right"]$25.80[/TD]
[/TR]
[TR]
[TD]Retail/Rental[/TD]
[TD="align: right"]$212,124[/TD]
[TD][/TD]
[TD="align: right"]$15.60[/TD]
[/TR]
[TR]
[TD]Dining[/TD]
[TD="align: right"]$72,738[/TD]
[TD][/TD]
[TD="align: right"]$5.35[/TD]
[/TR]
</tbody>[/TABLE]


I'd be glad to supply an example file if someone wants to take a look at it.

 

[powerpivotpro]

Take a look at this post, make sure to read all the way to the end:

A/B Campaign Analysis with Start & End Date Slicers « PowerPivotPro

Where in your case, each "campaign" would be a holiday period instead.

If that doesn't seem like it will work for you, please send me a sample file as you suggested