none
filter a column

    問題

  • Good afternoon.

    Prompt please how to filter a column ' Date ' for example from 01.02.2011 till .
    Thanks.

    Example of a file

    http://files.mail.ru/Q6CNR8

    2012年4月21日 下午 04:46

解答

  • Unfortunately filtering date range is not supported (yet) in PowerPivot.

    As a workaround, you can

    1) create an integer calculated column that converts your date value to int value (eg. '01/02/2011' to '20110102'), then you can filter on this column; or,

    2) create a measure (eg. myDate) and make it like (=CALCULATE(SUM([MyColumn]), DATESBETWEEN([DATES], "start date", "end date")). Then use this measure as the slicer - but you have to update it everytime you want a different value.

    I'd recommend using the first option if I were you.


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

    • 已標示為解答 Egoriy 2012年4月26日 上午 11:13
    2012年4月26日 上午 02:41

所有回覆

  • Hello Egoriy,

    It seems that you want to filter the records between two rangs, you can switch to Query Editor by clicking the table properties, then in the query editor you can edit your query at your will.

     


    Challen Fu

    TechNet Community Support

    2012年4月23日 上午 09:42
  • Good afternoon, Challen Fu thanks for the answer. But I have no access to write inquiries to a database.
    I would like to understand, whether it is possible in PowerPivot to do column group with dates (on months, quarters, weeks, years) as it is possible to do it in Excel2010? I apologize for my bad English.
    2012年4月24日 上午 07:31
  • Unfortunately filtering date range is not supported (yet) in PowerPivot.

    As a workaround, you can

    1) create an integer calculated column that converts your date value to int value (eg. '01/02/2011' to '20110102'), then you can filter on this column; or,

    2) create a measure (eg. myDate) and make it like (=CALCULATE(SUM([MyColumn]), DATESBETWEEN([DATES], "start date", "end date")). Then use this measure as the slicer - but you have to update it everytime you want a different value.

    I'd recommend using the first option if I were you.


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

    • 已標示為解答 Egoriy 2012年4月26日 上午 11:13
    2012年4月26日 上午 02:41
  • Chu Xu many thanks for the answer!
    • 已標示為解答 Egoriy 2012年4月26日 上午 11:13
    • 已取消標示為解答 Egoriy 2012年4月26日 上午 11:15
    2012年4月26日 上午 11:13