Forum Discussion

reast's avatar
reast
Icon for Helper II rankHelper II
10 years ago

Filter Active Clients by Date Range

I'm trying to figure out how to use the time slicer to filter active clients. They don't have a single date field to link with the calendar. They have a start date and an end date (or it's blank if t...
Need Help
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

How about this.

 

Fill you blanks in the End Date column with something like 31/12/9999

Create 2 new tables that consist of a single column of the dates you want to filter on, Start[Date] and End[Date]

 

Put a slicer on the Start[Date]

Put a slicer on the End[Date]

Write these helper measures

 

Selected Start = lastdate(Start[Date])

Selected End = lastdate(End[Date])

 

Write a measure that filters the customers.  Something like this.

 

=Calculate(countrows(customers),filter(customers, customers[Start Date] >=[ Selected Start] && customers[End Date] <= [selected End]))

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

Did you complete this step?

 

 

MattAllington wrote:

 

Create 2 new tables that consist of a single column of the dates you want to filter on, Start[Date] and End[Date]

 

 

  • reast's avatar
    reast
    Icon for Helper II rankHelper II
    10 years ago

    But those calendars still link to the start and end dates? This is what I'm getting. We should have over 300 clients at any given time, but this is only counting those with that specific intake, not including prior intakes that are still open.

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      10 years ago
      reast wrote:

      But those calendars still link to the start and end dates?

      No


      the dates I am talking about come from the 2 new single column tables you need to create.  Do not connect them to any other table.  Just build the measures as suggested