Forum Discussion
Events In Progress Problem - How long each day are work orders open each day
I have a set of work orders that may remain open across multiple days. I want to calculate:
- The number of hours each work order is open per day, and
- The total downtime per day, calculated by summing the open hours of all work orders for that date.
I plan to do this using a DAX formula. Below is a small sample of the data.
| Work Order ID | Equipment Name | Open Date | Closed Date |
| A1 | Thing 1 | 4/16/2026 10:00 | 4/16/2026 12:00 |
| B1 | Thing 2 | 4/16/2026 15:30 | 4/17/2026 8:48 |
| C1 | Thing 2 | 4/17/2026 11:10 | 4/17/2026 15:21 |
| D1 | Thing 1 | 4/17/2026 13:49 | 4/18/2026 11:27 |
| E1 | Thing 3 | 4/16/2026 14:15 | 4/18/2026 9:38 |
| F1 | Thing 2 | 4/18/2026 10:30 | 4/18/2026 18:23 |
| G1 | Thing 5 | 4/16/2026 21:15 | |
| H1 | Thing 6 | 4/17/2026 8:30 | |
| A2 | Thing 5 | 4/15/2026 10:30 | 4/16/2026 5:01 |
| B2 | Thing 4 | 4/16/2026 16:15 | 4/17/2026 2:10 |
| C2 | Thing 4 | 4/17/2026 5:00 | 4/17/2026 21:21 |
| D2 | Thing 6 | 4/17/2026 15:25 | 4/18/2026 3:33 |
| E2 | Thing 7 | 4/16/2026 19:25 | 4/18/2026 2:16 |
| F2 | Thing 3 | 4/18/2026 0:01 | 4/18/2026 8:12 |
| G2 | Thing 8 | 4/16/2026 0:00 | |
| H2 | Thing 9 | 4/17/2026 2:15 |
| Date | 16-Apr | 17-Apr | 18-Apr |
| Hours Open | 9.75 | 24.00 | 9.63 |
| Calculation | BOD 4/17 - Open Date | 24*(4/18 - 4/17) | Closed Date - BOD 4/18 |
- For this you can use the classic events in progress overlap calculation. With a disconnected Date table in the model, write a measure that, for each day in scope, intersects each work order's open interval with that day and sums the hours.
Open Hours =
VAR DayStart = MIN ( 'Date'[Date] )
VAR DayEnd = DayStart + 1
RETURN
SUMX (
FILTER (
ALL ( 'Work Orders' ),
'Work Orders'[Open Date] < DayEnd
&& ( ISBLANK ( 'Work Orders'[Closed Date] ) || 'Work Orders'[Closed Date] > DayStart )
),
VAR OpenAt = 'Work Orders'[Open Date]
VAR CloseAt = IF ( ISBLANK ( 'Work Orders'[Closed Date] ), NOW (), 'Work Orders'[Closed Date] )
VAR EffStart = IF ( OpenAt > DayStart, OpenAt, DayStart )
VAR EffEnd = IF ( CloseAt < DayEnd, CloseAt, DayEnd )
RETURN ( EffEnd - EffStart ) * 24
)
Drop Date[Date] on rows for the daily total. To get hours per work order per day, also add Work Order ID on rows; the same measure works because the row context narrows SUMX to that single order. Replace NOW() with TODAY() + 1 if you want still open work orders to count up to end of day rather than the current minute.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
3 Replies
- For this you can use the classic events in progress overlap calculation. With a disconnected Date table in the model, write a measure that, for each day in scope, intersects each work order's open interval with that day and sums the hours.
Open Hours =
VAR DayStart = MIN ( 'Date'[Date] )
VAR DayEnd = DayStart + 1
RETURN
SUMX (
FILTER (
ALL ( 'Work Orders' ),
'Work Orders'[Open Date] < DayEnd
&& ( ISBLANK ( 'Work Orders'[Closed Date] ) || 'Work Orders'[Closed Date] > DayStart )
),
VAR OpenAt = 'Work Orders'[Open Date]
VAR CloseAt = IF ( ISBLANK ( 'Work Orders'[Closed Date] ), NOW (), 'Work Orders'[Closed Date] )
VAR EffStart = IF ( OpenAt > DayStart, OpenAt, DayStart )
VAR EffEnd = IF ( CloseAt < DayEnd, CloseAt, DayEnd )
RETURN ( EffEnd - EffStart ) * 24
)
Drop Date[Date] on rows for the daily total. To get hours per work order per day, also add Work Order ID on rows; the same measure works because the row context narrows SUMX to that single order. Replace NOW() with TODAY() + 1 if you want still open work orders to count up to end of day rather than the current minute.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
BTW Forgot to add, thank youi so much for really prompt response! Can you recommend a video with a good explanation of these concepts?
Of course
I like this explainer:
https://youtu.be/YL7H1Rqckb0?si=jvgedej1JDMWvfU1
It's a bit old yet still relevant
Hope that helps!
