topic Re: Hour Duration between two different rows from different columns in Power BI in DAX Commands and Tips

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

sushmitasur4 — Wed, 21 Feb 2024 14:56:06 GMT

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.

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

Greg_Deckler — Wed, 21 Feb 2024 15:03:22 GMT

sushmitasur4 Cross posted. See answer in other thread.

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

Anonymous — Thu, 22 Feb 2024 07:39:26 GMT

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

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

sushmitasur4 — Thu, 22 Feb 2024 09:35:36 GMT

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

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

Anonymous — Fri, 23 Feb 2024 02:36:26 GMT

Hi sushmitasur4 ,

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

Best Regards

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

sushmitasur4 — Wed, 28 Feb 2024 14:22:38 GMT

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)