Excel 5-Calendar Date Table

Guest Post by Colin Banfield [LinkedIn]

image

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.

SNAGHTML2b298f56

Fig 1a – Date Dimension Wizard Step 1

 

SNAGHTML2b2aeb0c

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.

 

image

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.

11 Responses to Excel 5-Calendar Date Table

  1. Sunflowers says:

    Thanks!

  2. ColinBanfield says:

    You’re quite welcome.

  3. David Hager says:

    Thanks!

  4. ColinBanfield says:

    Hey David, you’re quite welcome too :)

  5. Donald Parish says:

    Thanks. I’d always had trouble with the 4-5-4 retail calendar in Analysis Services. This should be very useful.

  6. ColinBanfield says:

    Don, I’ve not sure in what sense you mean that you have trouble with the 4-5-4 calendar in AS. Be aware that the date table is designed to produce the same results as generated by the AS Date Dimension Wizard.

  7. Calendar Data tables are actually probably never ever used but this is a good post on them. When it comes to planning they’re totally worth it.

  8. Carsten says:

    Hi,

    thanks for this great article and template. I am following instruction 7-b but have issues with step 7-b-iv. After I selected with 7-b-iii the workbook (DB Vers. 12.0) I click okay. Under SELECT A DEFAULT TABLE I do not see ‘DimDate’. I see some others like ‘FirstDayOfWeek’.

    Any idea why?

    PS: I did not alter the file itself other than saving it under a different name.

    Thank you.

  9. ColinBanfield says:

    Carsten:

    It’s possible that System Tables are not enabled (these are the worksheet tables). Complete the wizard at step iii. Your data source name will show up in the Choose Data Source dialog box. Double-click the name to display the Add Tables dialog box. Click the Options button, and in the Table Options dialog box, make sure System Tables is checked and click OK. The name DimDate$ should now show up in the Add Tables dialog box.

  10. Nice post. Thanks for this information. I really appreciate your work, keep it up.

  11. David says:

    Thanks a lot

    I am struggling a lot while dealing with my date table,the facts table and their relationship.

    I will give it a go.

    Gracias

Leave a Comment or Question