Creating a PowerPivot calendar in Access or Excel
This blog shows you how to create a calendar of dates for use in PowerPivot. Instructions are included for Excel and Access.

Posted by Andy Brown on 16 January 2013

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:

Drag down date to create sequence

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:

Creating a YEAR function

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.

Some Useful Excel Functions

Here are some functions you may find useful:

To obtain Use and copy down Example
Year =YEAR(A2) 2013
Month number =MONTH(A2) 1
Month name =TEXT(A2,"mmmm") January
Month name for sorting =TEXT(A2,"mm - mmmm") 01 - January
Day number =DAY(A2) 16
Day name =TEXT(A2,"dddd") Wednesday
Quarter ="Q" & INT(MONTH(A2)+2)/3) Q1

When you've finished creating your calendar, save it (eg as calendar.xlsx):

Calendar fill

Double-click with the cross shown to copy the various formulae down.

Importing the Calendar from Excel

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

Choose 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:

Excel file option

Choose to connect to Excel.

 

Browse to the calendar file you've just saved:

Choosing Excel file

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:

Choosing Excel worksheet

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:

Linking to Excel data

First choose to import Excel external data.

 

Now choose which Excel workbook you're importing:

Get external data - Excel

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:

Excel column 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:

Table of dates in design view

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:

Importing from Access

Choose this option to import tables from an Access database.

This blog has 0 threads Add post