Hour Duration between two different rows from different columns in Power BI

[sushmitasur4]

So I am working with Live Data in POWER BI. I need a Query.

What I really want is the Sum of Hours between (Start time when type = 3) and (End time when type = 3)

Basically, It starts when type = 3 and records the start time. The counter Goes on until type goes back to type = 2 and records the previous row and records the End time and then calculates the Duration between them. 

The Hour Duration column is my desired Output.

Output Explanation:

I subtract the end time 23-01-2020 11:39 with the start time of previous row 21-01-2020 09:33.

[Anonymous]

Hi @sushmitasur4 ,

You can follow the steps below to get it:

1. Add index column in Power Query Editor

= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

2. Create a calculated column as below

Column = 
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) - 1
        )
    )
VAR _prestart =
    CALCULATE (
        MAX ( 'Table'[Start] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( _count ), BLANK (), DATEDIFF ( _prestart, 'Table'[End], HOUR ) )

Best Regards

[sushmitasur4]

The table below is the reference. The Hour Duration is the expected Output actually.

[Anonymous]

Hi @sushmitasur4 ,

Why the Hours Duration is "01:06:00" when Index is 5? Could you please provide the related calculation logic?

Best Regards

[sushmitasur4]

Here's the Pseudo Code of the logic calculation I need:

BypassedHoursColumn =

When typecolumn = 3: Variable MinTime = Start_TimeStamp

When typecolumn !=3:

IF PreviousRow((End_TimeStamp)) == Non-Blank: 

Variable MaxTime = PreviousRow((End_TimeStamp)) and Store (MaxTime - MinTime)

IF PreviousRow((End_TimeStamp)) == Blank: 

Variable MaxTime = PreviousRow((End_TimeStamp)) and Store (NOW() - MinTime)

[Greg_Deckler]

@sushmitasur4 Cross posted. See answer in other thread.