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 ty...
Need Help
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

      • sushmitasur4's avatar
        sushmitasur4
        Regular Visitor
        2 years ago

        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)