Date filters RRS feed

  • 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.
    Monday, March 15, 2010 7:01 PM


All replies

  • Anybody?
    Tuesday, March 16, 2010 2:40 PM
  • Hi Kevin,
    Can you provide some additional details? Where do you need to filter for a date range?

    Tuesday, March 16, 2010 4:41 PM
  • 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
    Tuesday, March 16, 2010 8:17 PM
  • Is this a joke?

    What use is an analysis tool that doesn't support date ranges?!?
    Tuesday, March 16, 2010 9:05 PM
  • 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
    Tuesday, March 16, 2010 10:14 PM
  • 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'.

    Wednesday, March 17, 2010 5:29 PM
  • 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.

    Wednesday, March 17, 2010 9:20 PM
  • 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
    Thursday, March 18, 2010 12:41 AM
  • Vikram,

    I have already entered this request for the next release.

    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, March 18, 2010 12:42 AM
  • 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.
    Thursday, March 18, 2010 2:59 PM
  • 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?

    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, March 18, 2010 7:39 PM
  • 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


    Note how it broke it down into years and months


    Now for the powerpivot filter:



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




    Friday, August 13, 2010 1:57 PM
  • 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
    Friday, September 3, 2010 12:03 AM
  • Hi Chu,

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

    Many thanks


    Monday, October 11, 2010 2:08 PM
  • 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.
    Tuesday, December 13, 2011 7:49 AM
  • Mohammed and Chu : As of today, this is still not implemented.


    Can you let us know if this date filter option is now available in PowerPivot?

    Sunday, February 23, 2014 2:37 PM