Making a pivot table month filter

Question

I'm trying to compile some reports through an Excel pivot table connected to a MS SQL database. Now the data I'm working with consists of a bunch of dates formated as dd/mm/yy. I want to make a filter based on the months of these dates but I can't figure out how to do that.

Answer 1

Sure, it's kind of convoluted, which is why you might be having trouble. You can read about it in the KB.

So say you create your table with dates as rows and items as columns and you're taking the sum.

• Click, on the table, the down arrow on "Row Labels" (assuming these are the dates).
• You'll want to Filter by label (not filter by value) - between - 1/1/15 and 1/31/15 (for January).
• There are also custom options like "this month" and "YTD".

_{click to view larger}

Note - For an OLAP data source, date filters require the OLAP cube field hierarchy data type of time. If a date is entered as text in a text field, then the date filter is not visible.

Also related (not duplicate): How do you format the date filter selection box for Excel Pivot Table