locked
How to create dynamic date filter for power pivot report. RRS feed

  • Question

  • Hi All,

    I am having a data in SQL. I am fetching all the data in PowerPivot. My table have 4 columns. StartDate1,EndDate1, StartDate2 and EndDate2. 

    Now user will enter the perticular date in excel cell/TextBox in excel. This value will compare "In Between" condition with my 4 columns. According to that entred date (filtered) report will be generated.

    How can i achive this please suggest.

    Thanks in Advance.

    Wednesday, September 24, 2014 9:26 AM

Answers

  • AmolTotre,

    The entred date is not the best resolution, please use a slicer to select date.

    a.Pull a calendar table as following and add it to you PowerPivot.

    Date
    1/1/2013
    1/2/2013
    1/3/2013
    1/4/2013

    b.Design you measures like this:

    Measure:=Calculate( [EX] , Filter('DataTable',Countrows(Filter('CalendarTable','CalendarTable'[Date]>=[DATE_Start_Join]&&'CalendarTable'[Date]<[DATE_End_Join]))>0))

    if you can share more information about table name and your calculation, the function will be easier to understand.

    c.Add a slicer in you pivot table based on this date column. Result as :

    Thursday, September 25, 2014 6:14 AM

All replies

  • Hi AmolTotre,

    Could you clarify your requirement a bit further, perhaps by presenting some test data and describing what you would expect to happen based on the test data?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Wednesday, September 24, 2014 11:22 AM
  • Hi Michael,

    I am having 8 columns in my table. Out of which 4 are date columns.

    DATE_Start_Join

    DATE_End_Join

    DATE_Start_asign

    DATE_end_assign

    09-JUN-1980

    05-JUN-1980

    29-JUN-1980

    19-JUN-1980

    20-MAR-1982

    20-MAR-1982

    20-MAR-1982

    20-MAR-1982

    20-MAR-1982

    20-MAR-1982

    20-MAR-1982

    20-MAR-1982

    12-SEP-1984

    12-SEP-1984

    12-SEP-1984

    12-SEP-1984

    Now I am importing this data in pivot table. In pivot table I want to add a text box which will accept date as input.

    Once user enter the date in text box, the date entered in text box will match with the Four columns from table and show the filtered result.

    For eg. If user enter some date say 01/04/2005 then it should display result where date is 01/04/2005  from all 4 date columns.

    Waiting for your reply.

    Wednesday, September 24, 2014 11:38 AM
  • AmolTotre,

    The entred date is not the best resolution, please use a slicer to select date.

    a.Pull a calendar table as following and add it to you PowerPivot.

    Date
    1/1/2013
    1/2/2013
    1/3/2013
    1/4/2013

    b.Design you measures like this:

    Measure:=Calculate( [EX] , Filter('DataTable',Countrows(Filter('CalendarTable','CalendarTable'[Date]>=[DATE_Start_Join]&&'CalendarTable'[Date]<[DATE_End_Join]))>0))

    if you can share more information about table name and your calculation, the function will be easier to understand.

    c.Add a slicer in you pivot table based on this date column. Result as :

    Thursday, September 25, 2014 6:14 AM