overlap time calculation

[Namita12]

I have time entry data in the following format, and I need to detect overlapping entries for each member.

Objective:

• Identify and flag entries as overlpa or No Overlap.

• Calculate the duration of the overlap for each member.

• Compute total overlap duration per member.

• Derive actual hours by subtracting overlap duration from total hours.

I’ve reviewed some existing DAX-based solutions available in forums, but they didn’t work for my case. Could someone provide a working DAX approach to:

1. Flag overlap entries, and

2. Calculate overlap duration per member

[v-lgarikapat]

Hi @Namita12 ,

Thanks for reaching out to the Microsoft fabric community forum.

@Ashish_Mathur , @ryan_mayu , @Greg_Deckler , @NimaiAhluwalia 

Thanks for your prompt response

@Namita12 , 

I wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided by @Ashish_Mathur , @ryan_mayu , @Greg_Deckler , @NimaiAhluwalia  If you have any questions or need further clarification, please don’t hesitate to reach out.

We appreciate your collaboration and support!

Best regards,
Lakshmi.

[v-lgarikapat]

Hi  @Namita12 ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[v-lgarikapat]

Hi @Namita12 ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[ryan_mayu]

@Namita12 

the sample data can't be opened. What's the expected output based on the screenshot you provided?

[Namita12]

This is my expeted output a column to flag overlap entries and a column for overlap duration calculation .

Overlap_Flag2 =

VAR curMember = 'Table'[member_id]

VAR curStart  = 'Table'[start]

VAR curRow    = 'Table'[Index]

RETURN

IF(

    COUNTROWS(

        FILTER(

            'Table',

            'Table'[member_id] = curMember &&

            'Table'[Index] < curRow &&       -- only previous rows

            'Table'[start] <= curStart &&    -- previous row starts before current row

            'Table'[end_] > curStart         -- overlap exists

        )

    ) > 0,

    "OVERLAP",

    "NO OVERLAP"

)

Overlap_Duration_Hours2 =

VAR curMember = 'Table'[member_id]

VAR curStart  = 'Table'[start]

VAR curEnd    = 'Table'[end_]

VAR curRow    = 'Table'[Index]

VAR PrevRows =

    FILTER(

        'Table',

        'Table'[member_id] = curMember &&

        'Table'[Index] < curRow &&  -- only previous rows

        'Table'[end_] > curStart    -- overlap check

    )

VAR OverlapIntervals =

    ADDCOLUMNS(

        PrevRows,

        "OverlapStart", MAX(curStart, 'Table'[start]),

        "OverlapEnd",   MIN(curEnd, 'Table'[end_])

    )

VAR OverlapHours =

    SUMX(

        OverlapIntervals,

        DATEDIFF([OverlapStart], [OverlapEnd], MINUTE) / 60

    )

RETURN

IF(OverlapHours > 0, OverlapHours, 0)

above are the 2 meaures that i used and i think I am very close to the solution.

[Greg_Deckler]

@Namita12 Try this to get the duration of overlap ( 91 minutes ). I think the rest is fairly straight-forward. This is from the Overlap quick measure here: Overlap - Microsoft Fabric Community. Slightly modified:

Overlap = 
    VAR __Start = MIN('Table'[Start])
    VAR __End = MAX('Table'[End_])
    VAR __Result = 
    IF(ISBLANK(MAX([Start])),BLANK(),
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table1 = ALL('Table')
        VAR __Table2 = GENERATE(__Table,__Table1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF([Value]>=[Start] && [Value] <= [End_],1,0))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",SUMX(CURRENTGROUP(),[Include]))
    RETURN
        COUNTROWS( FILTER( __Table4, [Minute] > 1 ) )
    )
RETURN
    __Result

[NimaiAhluwalia]

Hello @Namita12 

I have tried to solve the output which you want not sure if that is what you asked for. If not, then it would be great to provide a sample output to understand what is being asked here.

https://drive.google.com/drive/folders/1GC8WPpGrbHypar11R4j-tPvSBJeJ8ZlC?usp=sharing

[Namita12]

This is a snapshot of my expected output. I calculated the overlap in Excel using the following formula:

=IF(SUMPRODUCT((($L$2:$L$7000=L887)*($O$2:$O$7000=INT(M887))*($N$2:$N$7000>M887)*($M$2:$M$7000<M887)))>0,"OVERLAP","NO OVERLAP")

Now, I am trying to implement the same overlap detection logic in Power BI.

The column Ad-July is the overlap duration

[Namita12]

sample data 

[danextian]

Hi @Namita12 Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[Namita12]

sample data 

[Ashish_Mathur]

Hi,

Share the download link of an MS Excel workbook with your expected result derived with formulas.  I will convert those Excel formulas into DAX measures.