Excel: Create a Calendar Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

One downside of Power Pivot is the inability to group daily dates up to months and years. The common workaround is to build a lookup table that contains every daily date from the earliest date to the latest date in your data.

Start with a Date heading in A1. Add the first date in A2. Grab the fill handle and drag down until you get to the last date in your data. Add additional columns as needed:

  • Year =YEAR(A2)
  • Month =MONTH(A2)
  • MonthName = TEXT(A2,"MMMM")
  • Weekday: =WEEKDAY(A2,1)
  • WeekdayName: =TEXT(A2,"DDDD")

Make this data set into a table and add it to the data model. Relate it to your Fact table.

The date table makes it possible to group daily dates to months or years. It will also make the Time Intelligence calculated fields in the pivot table easier to use.

For more resources for Microsoft Excel