Pivot Tables and Different Date Grouping

When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.

You can add extra columns to your data to enable separate date-based reporting. In the image below we have added two columns to the right to handle monthly reporting and quarterly reporting. The Quarter column assumes a July – June financial year.

Data Layout with dates

The formula for cell H2 is

=DATE(YEAR(A2),MONTH(A2),1)

This normalises the date from column A to the first day of the month and allows for monthly reporting.

The formula for cell I2 is

="Q"&CHOOSE(MONTH(A2),3,3,3,4,4,4,1,1,1,2,2,2)

The CHOOSE function saves having to do multiple nested IF functions to work out the Quarter number. The MONTH(A2) at the start of the CHOOSE function returns a number from 1 to 12. The numbers that follow represent the 12 sequential quarter numbers for each month from January to December. Months 1 to 3 are all 3; months 4 to 6 are all 4; months 7 to 9 are all 1 and months 10 to 12 are all 2. The CHOOSE function uses the first entry (a number from 1 to 12) to choose from the following 12 entries and returns that number from the sequence.

Now you can use the Date, Month and Quarter columns (fields) to create three separate date-based Pivot Table reports from the same data.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *