I have an Excel 2010 PivotTable pointed to some data in the workbook that includes a date. I can group the date field into year and month just fine. What I want to do is produce a chart of a filtered date range (e.g. the previous 12 months). However, whenever I try to apply a "Between" date filter to the grouped date field (either the Year or Month grouping), my table blanks the rows of the filtered-out dates, but continues to display the row labels.
To illustrate, the chart of the table ends up looking like the following (showing blank columns for the filtered-out dates):
I control the underlying data; I can add additional columns to filter on, if necessary. So far, my only solution has been to delete all the data outside my desired date range and add separate columns for the month and year to act as row labels in the pivot table (i.e. I am no longer grouping on my date field). This seems like an unnecessary effort - surely someone else has this same requirement? What setting(s) am I missing?