topic Re: Power BI Hour Difference between two different rows from different column with a condition in DAX Commands and Tips

Power BI Hour Difference between two different rows from different column with a condition

sushmitasur4 — Wed, 21 Feb 2024 14:57:30 GMT

So I am working with Live Data. I need a POWER BI Query to create a calculated column or Measure.

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: Power BI Hour Difference between two different rows from different column with a condition

Greg_Deckler — Wed, 21 Feb 2024 15:02:53 GMT

sushmitasur4 This is a variation on Previous Row. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Tough to say, but you might also need Cthulhu. Cthulhu - Microsoft Fabric Community

Can you post your data as text and provide the expected output?

Or maybe it's simple and you can just do this:

Measure = SUMX( FILTER( 'Table', [Type] = 3 ), [End] - [Start] )

Then convert to seconds and use Chelsie Eiden's Duration? Chelsie Eiden's Duration - Microsoft Fabric Community

Re: Power BI Hour Difference between two different rows from different column with a condition

sushmitasur4 — Thu, 22 Feb 2024 05:12:22 GMT

Hi Greg, this is the entire table. The Hour Duration is the expected Output

Type
2
3
2
3
3
2
3
2
3

Start
22-01-2020 18:59
20-01-2020 09:34
20-01-2020 09:34
21-01-2020 09:33
23-01-2020 11:38
23-01-2020 13:38
22-05-2020 17:38
23-05-2020 13:38
24-05-2020 17:38

End
22-01-2020 19:59
20-01-2020 10:34
20-01-2020 09:34
21-01-2020 10:33
23-01-2020 11:39
23-01-2020 15:38
22-05-2020 18:38
23-05-2020 15:38

Hours Duration
null
01:00
null
null
01:06
null
01:00
null
null

These texts are Column wise.