Note: Here's a more current topic about working with Dates in PowerPivot. For more information, read: The PowerPivot for Excel 2010 overview. or The PowerPivot for Excel 2013 overview.
When you create a PivotTable that is based on Excel data, you can group the data in the PivotTable. For example, the source data for a PivotTable might contain a column that stores the sales date, but you can also see the data grouped by quarters or months, rather than days.
Note: You can use grouping for other types of data, but I will focus on dates here.
Original article (before wiki edits) was written by Michael Blythe, Microsoft SQL Server Analysis Services Content Publishing Manager.
The following screenshots show the Grouping dialog box and the effect of grouping by months.
You can see that the table in the Field List has only a Sales Date column, but the PivotTable is now sorted by month – Excel interprets the dates and provides this ability to group by different time periods. This Excel feature is not supported for PivotTables that are based on PowerPivot data, but you can achieve the same effect by adding columns to your source table. If your source table already has columns that represent the grouping that you need, then skip to the end of this article to see a screenshot of a PivotTable that shows PowerPivot data grouped by months.
Here’s the data that was used for the Excel PivotTables in the screenshots above:
This is a small table, so you could just manually add columns for whatever time periods you want (such as a Month column) and fill them out with data for each row. But a lot of times this is not practical, so I will show you how to group by using calculated columns in the PowerPivot window. These calculated columns use Data Analysis Expressions (DAX), which is similar to the Excel function language. For more information, see Getting Started with DAX and Create a Calculated Column in the PowerPivot Help on TechNet Library.
To start: copy the table above, paste it into the PowerPivot window, and name it Sales. For information about pasting data into PowerPivot, see Copy and Paste Data in the PowerPivot Help.
Now that you have the data in PowerPivot, add a calculated column with the following DAX formula, then right click the column, and rename it MonthNumber:
Like in Excel, this function returns the month number (such as 2) not the name (such as February). If you only want to group by months and the month number is sufficient, you can stop here. If you want to expand on this a little more, you can add a lookup table that allows you to pull different values into the Sales table:
Copy the table above, paste it into the PowerPivot window, and name it TimePeriods. Now add a relationship between the two tables, using the Sales[MonthNumber] and TimePeriods[MonthNumber] columns. For information about creating relationships, see Create a Relationships Between Two Tables in the PowerPivot Help.
The TimePeriods table illustrates a way that you can handle grouping. You can also build a more general purpose date/time table, such as the one described here on the PowerPivot-Info Web site.
Now that the tables are related to each other, you can easily use data from the TimePeriods table in the Sales table. Add a calculated column with the following formula and rename the column Month:
Sales for February will now appear in a PivotTable under the heading (02) Feb.
You can now add two more columns –Quarters and Years – with the following formulas:
Like the MONTH formula I showed earlier, the YEAR formula can get what it needs from the Sales Date column and doesn’t require the TimePeriods table.
The formulas shown above are one way to tackle this problem, but the specific approach is less important than the general principle of finding an easy way to add the time period columns that you need for grouping. After you have added those columns, you can create a PivotTable based on the data and group it based on which columns you select from the PowerPivot Field List.
Create a PivotTable from the PowerPivot window. For information about creating a PivotTable, see this video on the Business Intelligence TechNet page. In the Field List, drag Sales Date to the Column Labels pane, Product Name to Row Labels, and Sales Amount to Values. Now clear the Sales Date column and drag the Month column to Column Labels instead.
As you can see in the following screenshot, data is now grouped by months.