In the source data for your pivot table, you can use the drop down filters in the heading cells, to show only the records for a specific date range.
For example, in the table shown below, you could show just the orders that were shipped this week.
However, if you put a date field into a pivot table’s Report Filter area, those dynamic date ranges, such as Today, Next Month, Yesterday, etc. – are not available.
In that filter, you can only select specific dates by checking the boxes.
Move Date to Row Area
If you’d like to use dynamic date filters in a pivot table, move the date field to the last position in the Row Labels or Column Labels area, instead of the Report Filters area.
Then, click the drop down arrow, and click Date Filters
Next, click on one of the dynamic date ranges, such as Yesterday, or This Week.
After you applying the dynamic date range filter for “This Week”, the pivot table only shows the orders that have been shipped this week.
Hide the Date Details
If you don’t want to see a row for each shipping date, you can collapse the pivot table field.
- Right-click on the ShipDate heading, and click Expand/Collapse
- Click on “Collapse Entire Field”
After collapsing the date field, the Region and Product names show, but the dates are hidden.
The ShipDate heading is still visible, and the filter icon shows that the date range filter is still applied.
Show the Dates Again
After hiding a date field, you show it again later, if needed. Either show all the dates, for just the dates for a specific product.
To see all of the dates again,
- Right-click on the ShipDate heading, and click Expand/Collapse
- Then, click on “Expand Entire Field”
To see dates for a specific product
- Click the plus (+) sign at the left of the product name.
- The dates for that product are shown, and other dates stay hidden
Video: Dynamic Date Filters
To see the steps for applying a dynamic date filter in a pivot table, and hiding the dates, you watch this short video.
Get the Sample File
To get the sample file for this tutorial, please visit my Contextures website: Pivot Table Date Filters
________________________