Forum Discussion
Helper IDisplay 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:
- 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
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)RETURNIF(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!
3 Replies
- rsaprano
Most Valuable Professional
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)RETURNIF(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!
deevoHi 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
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)