Pivot table year over year by month

In this example, a pivot table shows the year-over-year variance in sales for each month of the year. Change can be displayed as the numeric difference or as a percentage (this example).

Fields

The pivot table uses all two of the three fields in the source data: Date, and Sales. Because Date is grouped by Years and Months, it appears twice in the list, once as "Date" (month grouping), and once as "Years":

The Date field has been been grouped by Months and Years:

The resulting "Years" field has been added as a Column field. The Original "Date" field is configured as a Row field, which breaks down sales by month.

The Sales field has been added to the Values field twice. The first instance is a simple Sum of Sales, renamed to "Sales " (note the extra space at the end):

The second instance of Sales has been renamed "% change", and set to show a "% Difference From" value, based on the previous year:

Note: Column H values are empty since there is no previous year. It has been hidden to streamline the report.

Steps

1. Create a pivot table, change report layout to Tabular
2. Add Date field to Columns area, group by Years and Months
3. Add Date field to Rows area (shows months)
4. Add Sales to Values area as Sum
5. Add Sales to Values area, rename to "% Change"
   1. Show values as = % Difference From
   2. Base field = Year
   3. Base item = Previous
6. Hide first Change column (optional)

Notes

1. To show absolute change, set Show values as to "Difference From" in step 5 above.
2. If Date is grouped by more than one component (i.e. year and month) field names will appear differently in the pivot table field list. The important thing is to group by year and use that grouping as the base field.
3. As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year. Then add the Year field to the pivot table directly.