Forum Discussion
How to use start and end dates in a Pivot table instead of a timeline
It depends to a certain extent on how you intend that filtering to work. By that I mean, you could be wanting to filter on absolute dates; or you could be wanting to filter on extent of time between two dates, regardless of the absolute dates of the start and finish. And maybe there are some other possible combinations, but I'll use those two to illustrate at least two possibilities.
- If you are wanting to use a filter to have your Pivot Table summarize data only from March 1 to March 30 (or any other pairing of absolute dates) you could set up two pivot table filters, greater than one, less than the other.
- If you are wanting to use the dates to establish a 30 day interval regardless of when the interval starts, you could create another column that calculates that delta.
Now, if none of that makes sense (or you don't see how to do it), please upload a sample of your file so somebody here can see the actual layout of your data.
mathetes In the timeline control, I select all days from Monday to Friday of a week.
The question really is how to use variables in a pivot table, without the user having to select values in a timeline or slicer.
I'll post a file later.
As variant
Let say you have range of dates and source table
If range is not a table let add it to data model through PivotTable. Let build main PivotTable with adding it to data model.
In data model add calculating column to main table
and use it as the filter.
