digest
Short Summary
Main recommendation: Use a standard Excel PivotTable to group dates by month and year when that is all you need, since it takes only a few clicks. Power Pivot's PivotTable lacks the built-in Group feature, so grouping there requires building a calendar table, creating calculated columns, and defining a relationship to the fact table.
Key Points
- In an Excel PivotTable, you drag Date to Rows, right-click, choose Group, select Months and Years, and the revenue rolls up immediately.
- Power Pivot has no Group Date feature, so the same result requires building a separate calendar table of dates from 2015 through 2018.
- Power Pivot grouping requires calculated columns for Month Number, Month Name, and Year, written with DAX functions such as MONTH, FORMAT, and YEAR.
- A relationship between the calendar table and the Revenue fact table must be created in Diagram View before the month and year fields work correctly.
- Power Pivot is worth the extra steps for big data (half a million to ten million rows), multiple data sources, or frequently refreshed reporting models.
Details Worth Noting
- Concrete instructions: For a standard Excel PivotTable, insert a PivotTable (Alt+N+V), drag Date to Rows, right-click a date and choose Group, select Months and Years, click OK, then drag Revenue to Values. Change Report Layout under Design to Outline or Tabular, and apply Currency number formatting to the Revenue field (not the underlying data).
- Concrete instructions: For Power Pivot grouping, first add your data table to the Data Model (Alt+B, then Y, Y). Build a calendar table: find the min and max dates with =MAX and =MIN formulas, enter the first date (1/1/2015), use Series > Fill Columns with a step value of 1 to fill through 12/31/2018, convert the range to a table (Control+T), and name it DCalendar. Add that table to the Data Model.
- Concrete instructions: Add calculated columns in DCalendar: Month Number (=MONTH(DCalendar[Date])), Month Name (=FORMAT(DCalendar[Date],"mmm")), and Year (=YEAR(DCalendar[Date])). Hide Month Number from client tools so it does not appear in the field list.
- Concrete instructions: In the Month Name column, use Sort by Column to sort by Month Number so months appear January to December rather than alphabetically.
- Concrete instructions: In Diagram View, drag Date from the calendar table onto Date in the Revenue table to create the relationship. Afterward, set the Revenue column's currency format in Power Pivot so it carries through to the PivotTable, and hide the Revenue Date field from client tools.
- The speaker notes that in Power Pivot, setting currency formatting on the Revenue column flows through automatically to the PivotTable, which does not happen the same way in a normal Excel PivotTable.
- The video presents the grouping steps as a comparison and does not give timing or performance benchmarks for the two methods.
Bottom Line
Excel PivotTables are the faster choice for simple month and year grouping, while Power Pivot is the better fit for large, multi-source, or regularly refreshed reporting models despite requiring a calendar table, calculated columns, and relationships.
