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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NickIT91
Frequent Visitor

Calculating total hours logged (with overlapping minutes)

Hi all,

 

I am wanting to know if there is a way to calculate total hours logged by each worker in a given day? I have a total hours column, however, an employee can log time for different tasks that overlap.

 

E.g. task A time logged: 11.00am-11.30am

       task B time logged: 11.10am-11.45am

 

If i just calculate total hours as the sum, this will give me 75 mins (30+45), however the total time logged should actually be 45 mins (since they started at 11 and finished at 11.45).

 

Any help greatly appreciated

 

Thank you,

3 REPLIES 3

Hi, 

 

I have been looking for a solution to this but have not yet foudn the answer. Has anyone know of a post or a link to a website that can give me the answer?

 

Thanks

RB

Anonymous
Not applicable

@NickIT91 

 

If you have a task where you need to calculate total time but without counting overlapping periods multiple times, then the best way is to expand the periods into the required granularity (hours, minutes or seconds), and then just do DISTINCTCOUNT on their UNION. This is by far the best and THE FASTEST way to calculate such things because it only requires the Storage Engine to go to work. No Formula Engine needed.

amitchandak
Super User
Super User

@NickIT91 , can you share some raw data in the exact format, This needs overlapping period calculation based on employee and date and create min max time to take diff.

 

One idea is that if the task starts in between the previous task we can end the time the previous one.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors