Forum Discussion

sushmitasur4's avatar
sushmitasur4
Regular Visitor
2 years ago

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

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.

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      sushmitasur4
      Regular Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi sushmitasur4 ,

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

        Best Regards