Forum Discussion
overlap time calculation
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:
Flag overlap entries, and
Calculate overlap duration per member
12 Replies
- Ashish_Mathur
Super User
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.
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
- NimaiAhluwalia
Continued Contributor
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
- Greg_Deckler
Community Champion
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 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]RETURNIF(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)RETURNIF(OverlapHours > 0, OverlapHours, 0)above are the 2 meaures that i used and i think I am very close to the solution.
v-lgarikapatCommunity Support
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.
