Forum Discussion
Filter for active within time range
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?
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] ) ) )
3 Replies
Hi,
The result should also include 205 and 210. You may download my PBI file from here.
Hope this helps.
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.
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] ) ) )
