How to get downtime per day when there are multiple days between start and end date?

[SteveMBSDO]

Hello,

I could use some help with my challenge.

In my organization we report machine downtimes with a [start] date/time and [end] date/time.

Example:

Unit 10 has a downtime

It started on 2-1-2022 at 19:31:34

And ended on 29-1-2022 at 07:54:13

The duration was 636,3775 hours (or 26,52 days)

How can I make a graph (in Power Bi) which shows me the downtime for every single day? It's important not to loose the REASON column for filter purposes.

So:

First day of downtime: 4 hours 28  minutes and 26 seconds

Last day of downtime: 7 hours 54 minutes and 13 seconds

Every single day in between: 24 hours

Would result in:

Eventually I need a graph which shows the downtime per day per machine, stacked. I.e.:

Any help or suggestions are greatly appreciated!

Thank you.

[tamerj1 · Accepted solution]

Hi @SteveMBSDO 
Please refer to attached sample file with the proposed solution

Duration Time = 
VAR DurationSeconds =
    SUMX ( 
        VALUES ( 'Date'[Date] ),
        VAR CurrentStartTime = 'Date'[Date]
        VAR CurrentEndTime = CurrentStartTime + TIME ( 23, 59, 59 )
        VAR FilteredTable = FILTER ( 'Table', CurrentStartTime IN CALENDAR ( 'Table'[Start].[Date], 'Table'[End].[Date] ) )
        RETURN
            SUMX (
                FilteredTable,
                DATEDIFF ( MAX ( CurrentStartTime, 'Table'[Start] ), MIN ( CurrentEndTime, 'Table'[End] ), SECOND )
            )
    )
VAR Hours = FORMAT ( QUOTIENT ( DurationSeconds, 3600 ), "00" )
VAR Minutes = FORMAT ( QUOTIENT ( MOD ( DurationSeconds, 3600 ), 60 ), "00" )
VAR Seconds = FORMAT ( MOD ( MOD ( DurationSeconds, 3600 ), 60 ), "00" )
RETURN
    IFERROR ( TIMEVALUE ( Hours & ":" & Minutes & ":" & Seconds ), BLANK () )

Duration Time 2 = 
SUMX ( 
    VALUES ( 'Date'[Date] ),
    VAR CurrentStartTime = 'Date'[Date]
    VAR CurrentEndTime = CurrentStartTime + TIME ( 23, 59, 59 )
    VAR FilteredTable = FILTER ( 'Table', CurrentStartTime IN CALENDAR ( 'Table'[Start].[Date], 'Table'[End].[Date] ) )
    RETURN
        SUMX (
            FilteredTable,
            DATEDIFF ( MAX ( CurrentStartTime, 'Table'[Start] ), MIN ( CurrentEndTime, 'Table'[End] ), SECOND )
        )
) / 3600

[SteveMBSDO]

Great to see! Didn't think of that before. I was trying to split the input in multiple rows according to duration overflowing 24 hours. Thank you very much!