Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Deevo_
Resolver I
Resolver I

DAX Formula review only (Overlapping Start and End Dates by Roles and their specialisation)

Hi All,

Sorry I cannot provide sample data. Can someone please help me review the structure of this dax measure?

Background info:

  • 2 Data sources
    • Project costings (not in flight) have roles and specialisation combos and each combo has a work start and end date
    • Projects in flight also have roles and specialisation combos and each combo has a work start and end date

What the measure is suppose to do:

  • From a single selection list, a "project costing code" is selected.
  • The roles and specialisations within the "project costing code" are compared to the exact same roles and specialisations combos of "projects that are currently in flight" AND have overlapping work start and end dates.

How this measure is applied in the visual:

  • Apply this measure on the table filter and apply "is not blank".

The expected result

  • Return "projects that are currently in flight" AND have overlapping work start and end dates for the exact same roles and specialisation combo as the "Project costing".

DAX Measure:

Project Costing Has Role overlap with In Flight Projects = 
 
VAR SelectedCosting = SELECTEDVALUE('Projects (Costings)'[CostingDisplayName])
VAR SelectedCostingStart = SELECTEDVALUE('Projects (Costings)'[CostingStartDate].[Date])
VAR SelectedCostingFinish = SELECTEDVALUE('Projects (Costings)'[CostingFinishDate].[Date])
VAR InFlightProjectStart = SELECTEDVALUE('Projects (In flight)'[InFlightStartDate].[Date])
VAR InFlightProjectFinish = SELECTEDVALUE('Projects (In flight)'[InFlightFinishDate].[Date])
 
VAR SelectedCostingRole = SELECTEDVALUE('Projects (Costings)'[RoleName])
VAR SelectedCostingSpecialisation = SELECTEDVALUE('Projects (Costings)'[SpecialisationName])
VAR InFlightProjectRole = SELECTEDVALUE('Projects (In flight)'[RoleName])
VAR InFlightProjectSpecialisation = SELECTEDVALUE('Projects (In flight)'[SpecialisationName])
 
VAR HasOverlap = 
(
    ((InFlightProjectFinish <= SelectedCostingFinish && InFlightProjectFinish >= SelectedCostingStart) && 
(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation)) 
 
|| 
 
    ((InFlightProjectStart >= SelectedCostingStart && InFlightProjectStart <= SelectedCostingFinish) && 
(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation))
)
 
RETURN
IF(HASONEVALUE('Projects (Costings)'[CostingDisplayName]) && HasOverlap,"Overlap",BLANK())        
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Deevo_ ,

 

I have checked your code, and I am confused about how did you create relationship between two tables.

As far as I know, there could only one active relationship between two tables.
However, you need to use the projectname and role name to determine the start date and end date values.

I think selectvalue() for data in Projects (In flight) will not work.

I think you need to calculate the data with more filters like Projects (Costings)[Name] =  Projects (In flight)[Name] and Projects (Costings)[Role] =  Projects (In flight)[Role]. Then compare the dates in two tables.

If this reply still couldn't help you solve your issue, please share a easy sample file with us and show us a screenshot with the result you want.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Deevo_ ,

 

I have checked your code, and I am confused about how did you create relationship between two tables.

As far as I know, there could only one active relationship between two tables.
However, you need to use the projectname and role name to determine the start date and end date values.

I think selectvalue() for data in Projects (In flight) will not work.

I think you need to calculate the data with more filters like Projects (Costings)[Name] =  Projects (In flight)[Name] and Projects (Costings)[Role] =  Projects (In flight)[Role]. Then compare the dates in two tables.

If this reply still couldn't help you solve your issue, please share a easy sample file with us and show us a screenshot with the result you want.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Rico, Thanks for your reply. Sorry I am on a work computer and they have restrictions on personal drives (google drive, dropbox). I did create a file but now i cannot upload it.

 

Thank you for providing me with some ideas. I re-wrote one section of my DAX. I believe I now have what I need 🙂

 

From this:

VAR HasOverlap = 
(
    ((InFlightProjectFinish <= SelectedCostingFinish && InFlightProjectFinish >= SelectedCostingStart) && 
(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation)) 
 
|| 
 
    ((InFlightProjectStart >= SelectedCostingStart && InFlightProjectStart <= SelectedCostingFinish) && 
(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation))
)
 
To This:
 
VAR HasOverlap =
(InFlightProjectRole = SelectedCostingRole && InFlightProjectSpecialisation = SelectedCostingSpecialisation)

&&

(
    (InFlightProjectFinish <= SelectedCostingFinish && InFlightProjectFinish >= SelectedCostingStart)
||
    (InFlightProjectStart >= SelectedCostingStart && InFlightProjectStart <= SelectedCostingFinish)
)
lbendlin
Super User
Super User

Yes, you can provide sample data. Make an effort.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.