Filter for active within time range

[ThaddeusB]

I have a set of data like (plus additional columns:

Client	Entry Date	Exit Date
205	3/24/2018	3/26/2018
206	12/9/2009	7/26/2017
207	11/10/2017	12/21/2017
208	10/29/2017	11/1/2017
208	4/10/2018	5/11/2018
208	5/20/2018	7/17/2018
209	4/25/2017	7/18/2018
210	4/17/2018	4/18/2018
211	10/25/2017	10/26/2017

I would like to filter the data for people active in a selectable time frame.  For example, selecting the range 10/1/17-12/1/18 should include 207,208, 209, & 211.  Formally, the rule would be [Entry Date]<=filter end AND [Exit Date]>= filter start.

I set up a Calendar table containing the days in the date range and made a slicer with that.  I am struggling with setting up the measure to filter my visualizations.  Any suggestions?

[Ashish_Mathur]

Hi,

The result should also include 205 and 210.  You may download my PBI file from here.

Hope this helps.

[ThaddeusB]

Thank you both.  I had set up the filter very similarly, but was not getting the expected results.  My problem was that I was putting the measure ONLY in the visual filters of a pie chart and using count(client) for the values.  Putting count(measure) in the values section instead of count(client) fixed it.

[Zubair_Muhammad · Accepted solution]

@ThaddeusB

try something on these lines

See file attached as well

Then you can use the MEASURE as a visual filter

Measure =
COUNTROWS (
    FILTER (
        table1,
        Table1[Entry Date] >= MIN ( 'Calendar'[Date] )
            && Table1[Exit Date] <= MAX ( 'Calendar'[Date] )
    )
)