MrExcel.com - Your One Stop for Excel Tips & Solutions

Create a Calendar Table

March 15, 2023 - by

Create a Calendar Table

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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Blessing Ri on Unsplash