cancel
Showing results for
Did you mean:
Regular Visitor

## How to check if table of date ranges overlap with dates selected on slicer

Hi everyone!

So, I'm trying to figure out if a table of Activities with corresponding date ranges (i.e. start and end dates) have any dates which overlap with a selection from a Dates slicer. I'm not sure how to relate the Dates table to the Activities. As an illustration, these are the Activities:

 Activity Start Date End Date A 01-Jan-2019 31-Dec-2019 B 11-Oct-2018 24-Apr-2019 C 05-Mar-2018 26-Feb-2020 D 11-Aug-2019 05-Nov-2020 E 17-May-2019 16-Sep-2019 F 03-Mar-2018 05-Jun-2018 G 02-Jan-2020 01-Jan-2021

If I were to use my slicer like so:

 Start 1-Jan-19 End 31-Dec-20

then I should get the following Activities:

 Activity A B C D E

This is because each of these Activities share occurrence dates with the slicer dates.

Edit:

Ultimately, I'm trying to create a measure, ActivitiesCount, that can count the rows of this resulting set of Activities. For this example, ActivitiesCount would equal five (5).

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to check if table of date ranges overlap with dates selected on slicer

Using [ShowMeasure], defined previously:

```ActivitiesCount =
SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )```
7 REPLIES 7
Super User

## Re: How to check if table of date ranges overlap with dates selected on slicer

Try this:

1. Place Table1[Activity] in the rows of a visual table selecting 'Don't summarize'

2. Create this measure

```ShowMeasure =
IF (
MAX ( SlicerTable[SlicerColumn] ) < SELECTEDVALUE ( Table1[Start Date] )
|| MIN ( SlicerTable[SlicerColumn] ) > SELECTEDVALUE ( Table1[End Date] ),
0,
1
)```

3. Place [ShowMeasure] in the visual level filters of the table visual and select  'Showw items when the value' is --> 1

Highlighted
Regular Visitor

## Re: How to check if table of date ranges overlap with dates selected on slicer

Thanks, @AlB. That's pretty clever but what I'm really trying to achieve is a measure which can count all the Activities with dates occurring within the slicer date range. For the example I gave initially, my measure would return five (5) activities.

Is there a way that this can be achieved?

Super User

## Re: How to check if table of date ranges overlap with dates selected on slicer

Hi,

The Activity count should be 6 (not 5).  Activity G should also be included.  You may downloaded my PBI file from here.

Hope this helps.

Regular Visitor

## Re: How to check if table of date ranges overlap with dates selected on slicer

Thank you @Ashish_Mathur! You're right that it's six (6) - I put the wrong end date (should've been 31-Dec-2019) so I'll make an edit.

Your solution works but I forsee there being an issue with the table generated - it's only seven (7) activities yet it produces a table of over 2,300 rows, so I don't think it would be feasible for a system of thousands of actvities.

Any other suggestions?

Super User

## Re: How to check if table of date ranges overlap with dates selected on slicer

You are welcome.  No other suggestions.  Before you make any such assumption, please first try the solutio on your live dataset.

Super User

## Re: How to check if table of date ranges overlap with dates selected on slicer

Using [ShowMeasure], defined previously:

```ActivitiesCount =
SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )```
Regular Visitor

## Re: How to check if table of date ranges overlap with dates selected on slicer

@AlB , thanks! The semicolons don't work for me but if I switch them for commas it appears to work fine.