You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Create a calendar table for PowerPivot in Excel / Access

I've just blogged about creating a table for a calendar in SQL Server
- here's how to do the same thing in Excel or Access. Whether you're
creating a table in Excel or Access, the easiest place to start is Excel.

Creating an Excel Calendar

To do this, start by creating a dates column:

Click and drag on the autofill handle shown to create a sequence of dates. It's often a good idea to extend this into the future, so your calendar is futureproof.

Now create any additional columns you will frequently want to aggregate by.
Here's how to do this:

Type a formula referring to the top cell, and
copy it down.

When copying formulae down, don't click and drag on the autofill handle;
instead, double-click on it, and Excel will automatically copy your formulae
down to the last date in column A.

Importing the Calendar from Excel

To link to the calendar in PowerPivot, choose to connect From Other Sources:

In PowerPivot, choose this button on the
Home tab of the ribbon.

Scroll all
the way down to the bottom of the list, and choose Excel:

Choose to connect to Excel.

Browse to the calendar file you've just saved:

Make sure you tick the box saying that your first row has column headers!

You can now give your imported sheet a friendly table name, and proceed as for
all other PowerPivot tables:

Here we've chosen to call the imported data
Calendar.

The disadvantage of using Excel is that you can't connect to the same table
twice in a PowerPivot workbook (so if you have two date fields in the same
workbook, you'd have to copy the Excel workbook and link to that instead for the
second date).

Creating a Calendar Table for Access

One way to do this would be to write lots of VBA to generate the table,
fields and data, but the easier way is to follow the steps above to create an
Excel calendar workbook. You can then import this as a table in Access:

First choose to import Excel external data.

Now choose which Excel workbook you're importing:

Browse to find the Excel calendar workbook, and choose to import it into a new table.

The rest of the wizard is pretty self-explanatory. It's worth tidying up
your data types:

Access assumes the year, for example, is of data type
Double, but it's actually an Integer.
However, you could leave all the types as their defaults and set
sensible defaults after importing the table into PowerPivot.

It's probably a good idea to set the date column as your primary key, as this
will check that you haven't inadvertently included the same date twice.

Eventually, you'll have a shiny new table in Access:

The calendar table in design view.

Connecting to the Access Calendar Table

Once you've created your calendar in Access, you can link to it in PowerPivot
as for any other table: