cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmeccles Regular Visitor
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:

ActivityStart DateEnd Date
A01-Jan-201931-Dec-2019
B11-Oct-201824-Apr-2019
C05-Mar-201826-Feb-2020
D11-Aug-201905-Nov-2020
E17-May-201916-Sep-2019
F03-Mar-201805-Jun-2018
G02-Jan-202001-Jan-2021

 

If I were to use my slicer like so: 

Start1-Jan-19
End31-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).

Can you help me please? Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

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

@jmeccles 

Using [ShowMeasure], defined previously:

 

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

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

Hi @jmeccles 

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

 

 

jmeccles Regular Visitor
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
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.

Untitled.png

jmeccles Regular Visitor
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
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.

Highlighted
Super User
Super User

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

@jmeccles 

Using [ShowMeasure], defined previously:

 

ActivitiesCount =
SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )
jmeccles Regular Visitor
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.