Display all Projects that have overlapping start and end dates based on a selected project

[deevo]

Hi All,

I am new to PowerBI and require some assistance after many hours of reading and going round in circles.

What I have:

• Projects table and a date table

What I am trying to achieve:

• I would like to be able to select any project name from a dropdown list
• Then I would like the selected project details to be displayed in the middle visual table
• Then I would like to display in the bottom table, all other projects that have an overlapping start and/or finish date of the selected project.
• In the below screenshot, I have selected "TestDish" and I expect to see the 3 highlighted projects because their start "and/or" end dates overlap.

Problem i am having:

• Writing my measure to find the overlapping start "and/or" end dates of any other project and to exclude the selected one

What I think I need:

• A measure that I can calculate if the start "and/or" end dates overlap, then return a 1 (true) or a 0 (false) next to each project  in a new column; and
• then apply this measure in the visual filter of the bottom table to only show the projects that have a value of 1.
• I have found this dax code and tried to implement this, but I feel abit lost.
  • Range = var StartRange=FIRSTDATE('Date Table'[Date]) var EndRange=LASTDATE('Date Table'[Date]) return if( SELECTEDVALUE(Projects[Start Date])>= StartRange && SELECTEDVALUE(Projects[End Date])<=EndRange && SELECTEDVALUE(Projects[End Date])<>BLANK(), 1,0)

I have shared the pbix file foir your viewing. it is just dummy data.

https://drive.google.com/file/d/1P40ibQunjiB5oAe-yOeNeobRN7L9I0if/view?usp=sharing 

[rsaprano · Accepted solution]

Hey Denny! There's probably quite a few ways to do this, some more elegant than others! Using Power Query would be a good option to find overlapping dates but to keep the solution simple (if somewhat inelegant!) I've gone for a duplicate of the projects table (called ProjectsDisconnected) which I hide but use in the "competing projects" visual.  I've then just calculated a DAX measure to work out if the project in this table (SELECTEDVALUE from ProjectsDisconnected) has overlap with the selected project from the main table/slicer (SELECTEDVALUE from Projects):

Has overlap with Existing Project =

VAR SelectedProject = SELECTEDVALUE(Projects[ProjectName])

VAR SelectedProjectStart = SELECTEDVALUE(Projects[ProjectStartDate])

VAR SelectedProjectFInish = SELECTEDVALUE(Projects[ProjectFinishDate])

VAR CurrentProjectStart = SELECTEDVALUE(ProjectsDisconnected[ProjectStartDate])

VAR CurrentProjectFinish = SELECTEDVALUE(ProjectsDisconnected[ProjectFinishDate])

VAR HasOverlap = (CurrentProjectFinish<SelectedProjectFInish && CurrentProjectFinish > SelectedProjectStart) || (CurrentProjectStart > SelectedProjectStart && CurrentProjectStart < SelectedProjectFInish)

RETURN

IF(HASONEVALUE(Projects[ProjectID]) && HasOverlap,"Overlap",BLANK())        

Then it's just a case of applying a filter to the "Competing Projects" table to have this measure not equal to blank. 

PBIX at CompetingProjects_WithOverlap.pbix

Not sure how performant this will be (depends on the number of projects you have!) but let me know if the logic works and if you need a more streamlined solution let me know and we can work out a better way to achieve the same thing!

[deevo]

Hi rsaprano,

Thanks so much for helping with this one. You have put me on the right path. I have updated your logic slightly as there were some missing results that I needed to include. I had to include the "=" operator in your query.

VAR HasOverlap = (CurrentProjectFinish<=SelectedProjectFInish && CurrentProjectFinish >= SelectedProjectStart) || (CurrentProjectStart >= SelectedProjectStart && CurrentProjectStart <= SelectedProjectFInish)

[deevo]

Hi rsaprano, I have been going through and checking the logic on this alongside your photo from the 2018 post that shows the 4 scenarios where the ranges overlap. Please correct me if I am wrong, but are we missing the scenario below where: (CurrentProjectStart<SelectedProjectStart && CurrentProjectFinish > SelectedProjectFinish)

Thank you