Hi. I am having trouble with date filters in a power pivot pivot table. Specifically, the pivot table does not respect the date filter to "This Month" in either the Calendar table or the data table. No matter what I try, all dates are presented when I add the Date to the Filters section of the pivot table. The worksheet in question is updated daily and has data going back several years. Accordingly, when I go to set the Date Filter to the current date, I have to scroll from the beginning date of the data set. I thought the Date Filter would remedy that particular issue. What am I missing?
Difficult to answer if you don't upload a file that demonstrates the problem.
Hi Riny,
The file in question has lots of tabs and lots of sensitive information. I don't know an easy way to share the file without compromising this information. With that caveat, I'll try to be more descriptive.
I have a Power Pivot pivot table. In the pivot table, Date is a Filter item. Account numbers are the only Rows and various descriptions from the source data determine the Columns. The worksheet is updated daily. Accordingly, the Date filter is always set to the day being updated. The data for this report goes back several years. As a result, the Filter field returns all dates for those years, requiring a simple, but tedious, scroll to the bottom of the list to check the current date (and uncheck the prior).
I was hoping the Date filters in the Data Model (set to "This Month") would flow through to the pivot table or, in the absence of that, a filter could be set at the pivot table level.
When I select "This Month" as a filter in the Data View of the model, the Date field filters accordingly. However, it doesn't translate to the pivot table. I don't see what purpose the filter in the Data View serves if it does not carry on to any pivot tables referencing that table in the Data Model.
I attached a doc with two snips to help illustrate. I couldn't snip with the Date filter field expanded. You have to trust me that it expands to the entire universe of dates in the file.
Thanks for your help!!
That's by design. As the name suggest the Data View is just a view. Changing the view does not change the data source for the pivot table. Not in Power Pivot and not in a regular pivot table either.
