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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
alessiobayware
Regular Visitor

Issue with Calculated Column for Overlapping dates

Hello everyone, on Power BI I'm trying to create a calculated column to detect if a Task is "Overlap" or "not". As in the image below for overlapping I mean when a Task Start Time and End Time overlap with another one like in the row 2 and 4. This overlap check should be done on each different User. So in the row 5 of the image below, that Task doesn't overlap with the row 4 because they are owned by two different users.

alessiobayware_0-1724425151309.png

 

The Calculated Column that I created is the one below

 

 

 

OverlapOrNot = 
VAR CurrentUser = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd = 'Task'[EndTime]
VAR ConditionOverlap = 
    CALCULATE(
        COUNTROWS('Task'),
        FILTER(
            'Task',
            'Task'[User] = CurrentUser &&
            (('Task'[StartTime] < CurrentEnd && 'Task'[EndTime]>CurrentStart ))))
RETURN
IF(ConditionOverlap>0,"Overlap","Not")

 

 

 

The problem with the formula above is that in the final result it gives "overlap" to each row on that column because when it start filtering, it compare the same Task with itself so of course will overlap.

 

My solution was to add this code line in the formula the variable for the "TaskName" and in the FILTER function to add the the Current TaskName is not equal to the one I want to compare.

 

 

 

OverlapOrNot = 
VAR CurrentUser = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd = 'Task'[EndTime]
VAR CurrentTask = 'Task'[TaskName]
VAR ConditionOverlap = 
    CALCULATE(
        COUNTROWS('Task'),
        FILTER(
            'Task',
            'Task'[User] = CurrentUser &&
            'Task'[TaskName]<>CurrentTask &&
            (('Task'[StartTime] < CurrentEnd && 'Task'[EndTime]>CurrentStart ))))
RETURN
IF(ConditionOverlap>0,"Overlap","Not")

 

 

 

 Using this updated formula cause me the issue that when I try to Save it show me the message "Working on it" to the infinity without any update, so I don't know why just by adding to the FILTER function the code line to excude the same Task for comparison, the Column isn't able to be saved. 
If someone can help me out to find a solution would be much appreciate it.


Thank in advance

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Maybe try it without the CALCULATE. I don't think you want that context transition.

 

OverlapOrNot =
VAR CurrentUser  = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd   = 'Task'[EndTime]
VAR CurrentTask  = 'Task'[TaskName]
VAR ConditionOverlap =
    COUNTROWS (
        FILTER (
            'Task',
            'Task'[User] = CurrentUser
                && 'Task'[TaskName] <> CurrentTask
                && ( ( 'Task'[StartTime] < CurrentEnd )
                && ( 'Task'[EndTime] > CurrentStart ) )
        )
    )
RETURN
    IF (
        ConditionOverlap > 0,
        "Overlap",
        "Not"
    )

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

Maybe try it without the CALCULATE. I don't think you want that context transition.

 

OverlapOrNot =
VAR CurrentUser  = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd   = 'Task'[EndTime]
VAR CurrentTask  = 'Task'[TaskName]
VAR ConditionOverlap =
    COUNTROWS (
        FILTER (
            'Task',
            'Task'[User] = CurrentUser
                && 'Task'[TaskName] <> CurrentTask
                && ( ( 'Task'[StartTime] < CurrentEnd )
                && ( 'Task'[EndTime] > CurrentStart ) )
        )
    )
RETURN
    IF (
        ConditionOverlap > 0,
        "Overlap",
        "Not"
    )

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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