Filter Active Clients by Date Range

[reast]

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 they are still active). I can't just link the start date because then it would only show clients the month they began and not the following months when they are still active. Any ideas would be greatly appreciated!

[MattAllington]

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]))

[reast]

Ok. Thank you for clarifying. Now I'm getting this error.

[MattAllington]

The first parameter of your filter function is a column - that is not allowed. You will note in my formula it is a table. 

[reast]

Thanks for your patience. I fixed the measure: Measure = Calculate(countrows('Clients'),filter('Clients', 'Clients'[INTAKE_DATE] >=[Selected Start] && 'Clients'[Discharge] <= [selected End])).

However, it still only seems to be pulling new clients in the date range, not all active clients (should be over 300).

[MattAllington]

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]

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]

---

@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

[reast]

Thanks for you response. With a start date slicer, if for example, they wanted to see all clients active in May 2016, they would have to check the start date for all dates prior to and including May 2016 and then for end date, blank and all dates after May 2016. That would work, but seems like a lot of work for the user. Or maybe I'm misunderstanding something.

[MattAllington]

---

@reast wrote:

Thanks for you response. With a start date slicer, if for example, they wanted to see all clients active in May 2016, they would have to check the start date for all dates prior to and including May 2016 and then for end date, blank and all dates after May 2016. That would work, but seems like a lot of work for the user. Or maybe I'm misunderstanding something.

---

My solution requires the user to select a single start date and a single end date. All dates between will be displayed. The biggest issue is the length of the slicers. 

[reast]

Then I'm definitely not understanding how to link it up. I thought the slicers linked to the start date and end date. As such, when I tested it, only clients with a start date of that particular date (or month, etc.) were included. Could you elaborate on the behind the scenes set up so I can get it to work properly? Thank you!