Excel pivot table using date column to filter results

Question

I have a Pivot Table which I have entered a date field into the Page Fields area to filter the data in the page.

However, I only have the option to select individual dates.

I want to use a date range. How can I do this? Or Can I do this?

Answer 1

Here is a classification by data source of how you can get / use the

Analysis Services

If you are using Analysis Services data, then I think you a breakdown of a date structure (if the dimension and the field is classified as such) by default.

(Hidden) Excel Sheet Data Source

Date Structure breakdowns are not readily available for data from another (usually hidden) sheet in the workbook.

You will have to create additional columns that mark ranges and then you can use it.

For example, lets say you have a SalesDate column. In your source, you should break the column further down so you get the the following columns

• SalesDate
• SalesYear
• SalesQuarter
• SalesMonth
• SalesWeek

Then you can use these to effectively create ranges. If you have custom ranges you can put them here as well.

External SQL Query

If you are using a SQL Query, you can create computed columns that do these calculations for you. If you have the ability to modify the SQL Query, you can add the columns to the query.

In case you don't have the ability to modify the SQL, you can create Calculated field in the Pivot Table structure.