SOLVED

Pivot Tables by Month an Year

%3CLINGO-SUB%20id%3D%22lingo-sub-2286500%22%20slang%3D%22en-US%22%3EPivot%20Tables%20by%20Month%20an%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286500%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20PivotTable%20to%20analyse%20Orders%2C%20Items%20Sold%2C%20Sales%2C%20Fees%2C%20New%20Sales%20by%20Month%20and%20Date.%20It%20works%20well%20from%20the%20start%20on%2026%20May%202020%20to%2031%20Dec%202020and%20listed%20months%20in%20order%20and%20can%20be%20expanded%20by%20date%20during%20the%20month.%20My%20field%20rows%20are%20Months%20and%20Date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20moving%20into%202021%20sales%20for%20Jan%202021%20appear%20before%20May%202020%20so%20the%20table%20reads%3C%2FP%3E%3CP%3E%26lt%3B26%2F05%2F2020%3C%2FP%3E%3CP%3EJan%20to%20Dec%20sequentially%3C%2FP%3E%3CP%3Ethen%20the%20grand%20total%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20not%20recognise%20the%20year%20difference%20as%20an%20entry%20for%20May%202021%20in%20merged%20with%20the%20May%20data%20for%202020.%3C%2FP%3E%3CP%3EHow%20can%20I%20get%20the%20PivotTable%20to%20show%20as%3C%2FP%3E%3CP%3E%26lt%3B26%2F5%2F2020%3C%2FP%3E%3CP%3EMay%202020%20to%20Dec%202020%20sequentially%3C%2FP%3E%3CP%3EJan%202021%20to%20Dec%202021%20sequentially%3C%2FP%3E%3CP%3Eand%20so%20on%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2286500%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286564%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%20by%20Month%20an%20Year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035776%22%20target%3D%22_blank%22%3E%40doonhamerbrownt121%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20any%20of%20the%20dates%2Fmonths%20in%20the%20pivot%20table.%3C%2FP%3E%0A%3CP%3ESelect%20Group...%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3EI%20assume%20that%20the%20check%20box%20for%20Months%20is%20ticked.%3C%2FP%3E%0A%3CP%3ETick%20the%20check%20box%20for%20Years%20as%20well%2C%20then%20click%20OK.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have created a PivotTable to analyse Orders, Items Sold, Sales, Fees, New Sales by Month and Date. It works well from the start on 26 May 2020 to 31 Dec 2020and listed months in order and can be expanded by date during the month. My field rows are Months and Date.

 

However moving into 2021 sales for Jan 2021 appear before May 2020 so the table reads

<26/05/2020

Jan to Dec sequentially

then the grand total

 

It does not recognise the year difference as an entry for May 2021 in merged with the May data for 2020.

How can I get the PivotTable to show as

<26/5/2020

May 2020 to Dec 2020 sequentially

Jan 2021 to Dec 2021 sequentially

and so on?

 

Any help will be appreciated.

 

 

 

2 Replies

@doonhamerbrownt121 

Right-click any of the dates/months in the pivot table.

Select Group... from the context menu.

I assume that the check box for Months is ticked.

Tick the check box for Years as well, then click OK.

best response confirmed by doonhamerbrownt121 (New Contributor)
Solution

@Hans Vogelaar Thank you for this, it worked. So simple when you know how. I spent many hours not getting there.