Forum Discussion

deevo's avatar
deevo
Icon for Helper I rankHelper I
4 years ago
Solved

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

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...
Need Help
  • rsaprano's avatar
    rsaprano
    4 years ago

    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!
rsaprano's avatar
rsaprano
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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's avatar
    deevo
    Icon for Helper I rankHelper I
    4 years ago

    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

  • deevo's avatar
    deevo
    Icon for Helper I rankHelper I
    4 years ago

    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)