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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
ryan_b_fiting
Post Patron
Post Patron

Calculating Overlap Hours in Timestamps /Timelogs

Hi Community - 

I am in need of some help on how to calculate total hours logged, but excluding time stamps that are overlapping by employee.  Here is a sample of the data:

ticket_idEmployeecompany_nametimeStarttimeEndHours LoggedHours Logged CorrectedOverlap Hours
8222055465Company ABX7/5/2022 12:007/5/2022 20:008.08.00.0
8222155465Company ABX7/5/2022 14:007/5/2022 19:005.00.05.0
8564855465Company FFF7/5/2022 12:007/5/2022 13:001.01.00.0

 

When I just SUM the hours here I get 13 hours, but the actual result I would want to see is 8 hours, because the entire 5 time log of the second row is overlapping the first log.

Line item in red I would expect 1 hour overlap and 0.0 Hours Logged Corrected

 

I am 99% of the way there with the below calculated column screenshot (referencing real data model not the sample above):

Hours Logged Corrected = 
VAR CurrentStart = '2 - CW - TimeEntry'[timeStart]
VAR CurrentEnd = '2 - CW - TimeEntry'[timeEnd]
VAR CurrentEmpolyeeTable = 
    CALCULATETABLE ( 
        '2 - CW - TimeEntry', 
        ALLEXCEPT ( '2 - CW - TimeEntry',  '2 - CW - TimeEntry'[member_name], '2 - CW - TimeEntry'[Start Date] ) 
    )
VAR PreviousRecords = FILTER ( CurrentEmpolyeeTable, '2 - CW - TimeEntry'[timeStart] < CurrentStart )
VAR PreviousStart = MINX ( PreviousRecords, '2 - CW - TimeEntry'[timeStart] )
VAR PreviousEnd = MAXX ( PreviousRecords, '2 - CW - TimeEntry'[timeEnd] )
VAR Starting = MAX ( CurrentStart, PreviousEnd )
VAR Ending = MAX ( CurrentEnd, PreviousEnd )
RETURN
    DATEDIFF ( Starting, Ending, SECOND ) / 3600

But as you see in the screen shot below, the first 2 lines have an exact same starttime which is causing an issue.  The second line should hace 0.33 overlap hours for the 20 minutes that overlap, but it is showing 0 because it has the same starttime.  I have tried using startime<=CurrentStart in the Previos_Records variable but that shifted ALL hours to overlap hours.

Log.PNG

 

Any help would be greatly appreciated.  I have tried so many things on this and have had no luck.  I have not had any luck getting answers from the community either.

Thanks Community!

Ryan

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @ryan_b_fiting 

 

You can try the following methods.
Column:

Hours Logged Corrected = 
Var mintime=CALCULATE(MIN('2 - CW - TimeEntry'[timeStart]),ALLEXCEPT('2 - CW - TimeEntry','2 - CW - TimeEntry'[Employee]))
Var maxtime=CALCULATE(MAX('2 - CW - TimeEntry'[timeEnd]),ALLEXCEPT('2 - CW - TimeEntry','2 - CW - TimeEntry'[Employee]))
Return
SWITCH(TRUE(),
[timeStart]<=mintime&&[timeEnd]>=maxtime,[Hours Logge],
[timeStart]>=mintime&&[timeEnd]<=maxtime,0,
[timeStart]<=mintime&&[timeEnd]<=maxtime,[Hours Logge])
Overlap Hours = [Hours Logge]-[Hours Logged Corrected]

vzhangti_0-1663317878497.png

 

 Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

@v-zhangti thanks for the message.  The result you are showing is what I would expect.  I created that column, and the Hours Logged Corrected is showing me 0 on every time entry, so for some reason it is not working for my data.  Any ideas why?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors