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

• 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