Date filters

Question

I'm only seeing 'Equals' and 'Does not equal' when I go to filter on any date fields... how does one filter for a date range, or is this not supported in the CTP? Thanks.

Answer 1

Anybody?

Answer 2

Hi Kevin,
Can you provide some additional details? Where do you need to filter for a date range?

Regards,
Linda

Answer 3

Unfortunately for this release you cannot do range filtering on a non-numeric fields. I feel your pain and I have asked the team to consider adding this in V2.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Is this a joke?

What use is an analysis tool that doesn't support date ranges?!?

Answer 5

Kevin,

You can work around by providing a query to select the data  within dates you need.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 6

I can't be re-writing queries for every permutation of date range that my end users want to analyze, Chu. That's not at all a reasonable solution, and kind of defeats the purpose of self-serve BI don't you think?

Linda, in response to the 'where' question.... ANYWHERE.

I set up a PowerPivot data source drawing transaction details, including amounts, dates, and other various details. The first thing my users are going to want is to be able to slice and dice this data by date ranges... how did we do last week, how does that compare to the week before, etc? Excel 2007 tables can do this using filters easily, it even automatically breaks every date column down into the hierarchy of year-month-day-etc. However, the failing point of Excel 2007 has always been that this (business) intelligence disappears when it comes to a pivot table. You're stuck with equals/does-not-equal as the only available filter options, and apparently PowerPivot is no different. I myself am more than capable of breaking each date field down into year/month/day/hour/minute/whatever to make it 'work' with Excel pivot tables, my users on the other hand are going to expect that a 'self-serve BI solution' is going to clue in on time dimensions. Having no intelligence on time dimensions is going to be a major sticking point for my org adopting this, and we most certainly won't be waiting around for a 'V2'.

Answer 7

Kevin, can you file this supportability item as a "bug" on the Connect site. It goes directly to the Dev group who can take this into consideration for a future release.

Cheers,
Vikram

Answer 8

Kevin,

Importing is the first step you run when you try to create a powerpivot workbook. Your end users will never do importing again.

If your end user wants to slice the workbook data by date, you can add appropriate date information (eg. Year, Month, or Day) to the slicer.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 9

Vikram,

I have already entered this request for the next release.

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 10

Chu, judging from your posts it seems a lot like we may be talking about two different things, or at the very least have some pretty different expectations of a 'BI' tool.

I'm talking about the functionality that is already available in Excel 2007, you'll see it when you click on the filter button of a date column in a table object. It will break down the entire date hierarchy for you, no need to add a new column to the table for each date field (like year/month/day). There are also a wide array of other options such as Before, After, Between (which I would REALLY REALLY like), Tomorrow, Today, Yesterday, etc, etc.

Now the only options I see for filtering a date ANYWHERE in PowerPivot, or a PowerPivot based pivot table/chart are equals and does not equal. If I add any date field to a slicer the only options I get are picking specific dates (or times if that info is in the field). If I add a date field to the filter then the only option I get is equals/does not equal. I don't see any way to base a filter or a slicer on 'appropriate date information (eg. Year, Month, or Day)' as you mention, without first having that date broken down in the data source.

Essentially it seems to come down to PowerPivot not having any support for a time dimension... and I have to say I'm rather confused why anyone would build a 'BI' tool without any support for one of the most crucial aspects of BI.

Answer 11

Kevin,

I thought you were talking about the Date filter in the import wizard. In Import Wizard, you are only able to choose "Equal" or "Not Equal to".

So you are talking about the PowerPivot in Excel main window: For example, you put a date field in the Row Label. Then you can use Label Filters to pick the dates you are interested in. Label Filters contain a lot more than just "Equals" or "Does not equal...", such as "Great Than...", "Between" etc. Does it solve your problem?

Thanks,
Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 12

Hi Chu,

 

This is what he is referring to and its a problem for me too, see 2 screenshots one which is on a standard date column filtered

http://i368.photobucket.com/albums/oo127/fionnyboy/DateExampleStandard.jpg

Note how it broke it down into years and months

 

Now for the powerpivot filter:

 

http://i368.photobucket.com/albums/oo127/fionnyboy/DateExamplePowerPivot.jpg

Just one long string, makes it most difficult to filter, also see missing date filter options

 

Regards

Fionn

Answer 13

I am happy to tell you guys that this feature has been implemented in the next version of PowerPivot :)

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 14

Hi Chu,

When are we likely to see this next version as this would be very useful to myself also?

Many thanks

Pete

Answer 15

I know this is an old thread, but I just installed PowerPivot 2012 RC0, and I don't see this implemented except that they added this Date Table which doesn't serve the purpose, I can only apply it to one column and it must be unique, but I have multiple date columns and they are not unique, and still I want leverage the date filter, when I insert a pivot table from the powerpivot data in excel.