Excel 5-Calendar Date Table

Guest Post by Colin Banfield [LinkedIn]

For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a “portable” date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch.  Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the “Ultimate Table” lacks fiscal periods. Much of the analysis work I do includes fiscal periods.

For the Excel table that I was building, I checked the validity of some of the date period formulas I created by using date tables generated from the Date Dimension Wizard in Analysis Services. In the process of generating these tables,  a thought struck me – why not duplicate the functionality of the Date Dimension Wizard using nothing more than Excel formulas? Hence, my  goal changed from from creating a Regular Date/Fiscal Date table to one that included the additional calendars used in businesses. If you are an independent consultant with a varied clientele , you cannot anticipate what calendars a client will need, so I decided to cover all bases. For those of you that are unfamiliar with the Date Dimension Wizard in Analysis Services (only available in Multi-Dimensional mode), Figures 1a & 1b show the first two steps of the wizard.

Fig 1a – Date Dimension Wizard Step 1

Figure 1b – Date Dimension Wizard Step 2

As you can see from the above figures, you can generate up to five different calendars. Adding periods for the ISO calendar in the Excel date table was straightforward, but I came to a grinding halt when I turned my attention to the reporting calendar. The problem with the reporting calendar was that I couldn’t find any consistent definition of this calendar. I checked for information on this calendar online, and I contacted some folks familiar with the 4-4-5 pattern of the calendar. However, there was no consistency in the information I gathered. At this point, I put the reporting and manufacturing calendars on hold, and used my new table with the other three calendars. Every so often, I returned to tackle the reporting calendar, but to no avail. Eventually, I realized that I was using the wrong approach for creating this calendar. Since the Dimension Wizard obviously generated reporting periods using well defined criteria, all I had to do was “reverse engineer” the generated results. Using this approach, I was able to complete the reporting and manufacturing calendars without further issues. Figure 2 shows the configuration options in the final version of the Excel date table. This entry form was created on a worksheet separate from the date table.

Figure 2 – Excel Date Table Configuration Options

In addition to the options available in the Dimension Wizard, I have included the ability to define weekend days, and one or more holiday tables. Another option (not shown) is the ability to create simple “selling season” periods. The actual date table is totally configurable – change the column titles and text that appears in the columns to suit your needs (including using a different language). Add or remove date period columns, change the year window of the calendar (it’s 21 years by default), and so on.

Anyone interested in using the date table can download it here. The workbook includes detailed information and instructions regarding the use of the table.