Forum Discussion
Advocate ISum hours With Combined Period
Hello,
I'm trying to sum the amount of hours a person is spending on some certain tasks.
They can work in more than one task at a time.
Example:
Tasks A - start 01/01/2025 08:00 end 09:00
Tasks B - start 01/01/2025 08:30 end 09:00
Tasks C - start 01/01/2025 11:00 end 12:00
I need the result to be 2 hours and not 2,5 hours.
Is there a way I can get to the result with a dax measure?
Data sample
crsjunior Hi!
Step 1: Create a minute-level helper table
Create a disconnected table with one row per minute (or per 5 minutes if performance matters):
TimeSlots = ADDCOLUMNS ( GENERATESERIES (DATETIME(2025,1,1,0,0,0), DATETIME(2025,1,1,23,59,0), TIME(0,1,0)), "Time", [Value] )
Step 2: Create a measure to mark whether the person was working at that minute
Assume your main table is called Tasks, with [Person], [Start], and [End] columns.
WorkingFlag := VAR CurrentTime = SELECTEDVALUE ( TimeSlots[Time] ) RETURN IF ( COUNTROWS ( FILTER ( Tasks, Tasks[Start] <= CurrentTime && Tasks[End] > CurrentTime ) ) > 0, 1, 0 )Step 3: Total unique working hours
Total Unique Hours := DIVIDE ( SUMX ( TimeSlots, [WorkingFlag] ), 60 )This gives you unique hours worked, regardless of overlapping tasks.
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
3 Replies
- BeaBF
Super User
crsjunior Hi!
Step 1: Create a minute-level helper table
Create a disconnected table with one row per minute (or per 5 minutes if performance matters):
TimeSlots = ADDCOLUMNS ( GENERATESERIES (DATETIME(2025,1,1,0,0,0), DATETIME(2025,1,1,23,59,0), TIME(0,1,0)), "Time", [Value] )
Step 2: Create a measure to mark whether the person was working at that minute
Assume your main table is called Tasks, with [Person], [Start], and [End] columns.
WorkingFlag := VAR CurrentTime = SELECTEDVALUE ( TimeSlots[Time] ) RETURN IF ( COUNTROWS ( FILTER ( Tasks, Tasks[Start] <= CurrentTime && Tasks[End] > CurrentTime ) ) > 0, 1, 0 )Step 3: Total unique working hours
Total Unique Hours := DIVIDE ( SUMX ( TimeSlots, [WorkingFlag] ), 60 )This gives you unique hours worked, regardless of overlapping tasks.
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
V-yubandi-msftCommunity Support
- V-yubandi-msft
Hi crsjunior ,
Could you let me know if your issue has been resolved or if you still need any more information? If you need further help, please let us know.