How to Group dates in PowerPivot

Hi Tsodui,

Thank you for the forum question.

We are working with a data model when we are working with PowerPivot. Microsoft has integrated database technology in Excel. The whole idea with PowerPivot is that it can handle data cubes (multi dimension data) and we have to please Excel to get it right.

The tables should be normalised (all data in one table should be a fact of the primary key (the unique record identifier, which we use in the relationship to other tables in the data model)).

As we did on the course. If you want to work with dates in your report, you will need a date table in a external workbook, where the primary key is all the dates starting from the date you want to start your report/PivotTable from and a end date some years into the future.

Type Dates in A1 in the worksheet. Type 1/1/2010 in A2. Drag down the fill handle as far as you want. Type Month in B1. Type =Month(a2) in B2 and double click the fill handle.

Then you will need to add as many columns as you need. If you want to group your report on quarters you will need a column extracting the quaters from the dates (primary key). If you want to group by year you must have a column extracting the year from the primary key.

Import the date table to your data model and relate the primary key to a date in your table.

If you in the pivottable add month from your imported date table Excel will group your data on month.


When you are using DAX measures, which need a date you must reference the primary key in the date table.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Tsudoi,

Your tables must be organised as Lookup tables (dim tables) and transaction tables (fact tables).

A lookup table could be a product table where you have all your products identified by a product ID (the primary key). The table should have all the product properties (price, colour etc.). A fact table could be all the transactions. Product id (to identify which product you have sold), Quantity, sales date, discount all information for each transaction.

Then the tables can be related from the Product id from the product table to the product id in the transaction table and you will have a data model, where the dax measures can handle the cube array calculations.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector