Events In Progress Problem - How long each day are work orders open each day

[amcooper]

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

Some work orders span multiple days, so the number of open hours must be split by day. For example, work order E1 is open across three days, and the hours are calculated differently for each day:

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

I’ve found examples online (often using hotel guest data) that calculate how many records are open per day or during a time range, but they don’t address calculating hours per day for records that span multiple days. I’m struggling with where to start for this scenario.

[Shai_Karmani · Accepted solution]

• 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

[amcooper]

BTW Forgot to add, thank youi so much for really prompt response! Can you recommend a video with a good explanation of these concepts? 

[Shai_Karmani]

Of course 

I like this explainer:

https://youtu.be/YL7H1Rqckb0?si=jvgedej1JDMWvfU1

It's  a bit old yet still relevant 

Hope that helps!