Mar 04 2021 08:14 AM - edited Mar 04 2021 01:51 PM
Update: Just added example file 2:21 EST
I have a data set of about 12 Columns and 4100 Rows with a "Billing Cycle Date" Column formatted to date format d/mm/yyyy (not that that matters). Source data contains data for 2020 and (now) 2021. I'm targeting data only for DEC 2020, JAN 2021 and FEB 2021. All Billing Cycle Date Cells are populated with dates formatted as: "2/23/2021", "1/23/2021" or "12/23/2020" and each record is grouped together by their respective dates.
I've filtered and grouped by every means thinkable 2) I copied source data to a new sheet / deleted the old sheet - redefined the range 3) I've deleted "vacant" cells at the bottom of the data set. Etc. The Date filters don't seem to want to Display FEB data either... UNLESS I move Feb data to a separate spreadsheet and Pivot Table on that month alone... (?)
No matter what I've tried, I can't get the latest and most recent 2/23/2021 data to appear in the Pivot Table to where the it displays all three month's summarized data (just DEC and JAN). When I do fumble around with groupings enough, I can eventually get all three month names to appear in the table but February's data in the Pivot Table is blank (even through the entire February data set is populated in the source sheet). Any thoughts as to why this is happening?
DEC
JAN
FEB
Ex:
Number. Bill Cycle. Cost. Etc... --->
123-098-7891 12/23/20 $39.00. xxx
567-456-7687 12/23/20 $39.00. xxx
321-678-7456 12/23/20 $39.00 xxx
123-456-7891 1/23/21 $39.00
678-456-7687 1/23/21 $39.00
321-456-7456 1/23/21 $39.00
123-456-7891 2/23/21 $39.00 Etc
678-456-7687 2/23/21 $39.00 Etc
321-456-7456 2/23/21 $39.00
Mar 04 2021 08:41 AM
@MikeM Difficult to diagnose without seeing your file. Can you upload a sample file with say a hundred rows of data spanning, say November 2020 through February 2021. Remove any confidential information, but leave the dates as you have them in your actual file.
Mar 04 2021 11:23 AM
Mar 04 2021 12:58 PM - edited Mar 04 2021 01:01 PM
@MikeM Not sure I understand. You indicate that you expect to see three months for the first item in the PT. When I filter cost centre 12345 for USER 1, I find only one entry for 23 March 2020. Why do you expect to see the months of Dec 2020 through Feb 2021?
I recreated a PT resembling yours, filtering dates (right-click on a month row header and select "date filter) for 1 Dec 2020 through 28 Feb 2021. It doesn't seem to be a very meaningful PT to me, but perhaps it is to you. Let me know if this is what you had in mind. See attached (MASTER DATA). But perhaps the PT in the other file makes more sense.
Mar 04 2021 01:50 PM
Mar 04 2021 02:06 PM
Perhaps you may use this setting for dates field
and show zero for empty in PivotTable settings
Mar 05 2021 08:43 AM