Pivot table cannot find difference between January current year to December of previous year. Excel 2010

Question

I am weeks into making my first ever pivot tables, cant believe the power of them but have hit a snag I cannot overcome.

I have a worksheet which has a column with raw data (manually imputed)

Then I have another 2 columns that calculate the difference from the previous month and the % difference. This works fine.

As you can see January 2015 is blank as the difference field is looking for the previous month and January is the first month.

how can I get it to understand to look at December 2014

Answer 1

When there are two different levels of grouping, i.e. Year and Month, it is not possible to get Excel to compare the last item of one group with the first item of the previous group.

Excel treats the additional level of grouping as a different field, which is visible when looking at the PivotTable fields. The Years of the Date show as a separate field:

When choosing to calculate the difference, Excel can only compare to values within the same field. Note that you can compare to a specific month value, but not a specific month of a specific year:

Similarly, Excel can't compare to values from the previous grouping, even though human-logic knows it's the previous value in the overall sequence.

To see the difference between each month across years, it's necessary to have all Year-Months in a single field. This isn't possible using PivotTable grouping on normal dates.

The workaround requires a new field to be added to the data which contains a single value per Year-Month. The PivotTable will then naturally group the data by these Year-Month values.

There are two ways this can be achieved:

1. Calculate the first day of each Year-Month

Insert another column with this formula (referring to a date in the A2 cell):

=Date(Year(A2),Month(A2),1)

This performs the grouping before the data goes into the PivotTable by treating every date as the first of the month. In the PivotTable, the full date will appear by default. You can apply Custom Number Formatting to the field to display only the Month and Year, for example: "mmm yyyy"

This is a better solution when grouping by months as it allows more formatting options.

2. Calculate a text value for each Year-Month

Insert a column with this formula:

=TEXT(A2,"yyyy mm")

For example, the following data:

Can generate this PivotTable (with sums and then differences):

The text solution has the drawback that you must specify the Year and then Month numerically (and padded to 4 and 2 digits respectively) if you want the PivotTable to automatically order the values in chronological order.

The benefit of the text solution is greater flexibility. For example, you could group bi-monthly with more advanced formulas or group two fields of actual data together by concatenation.