Calculation of Sustainability in duration for the following data

[ZB_tech]

In this i need the sustainability time in minutes for each damper position. i.e., if my damper position 60  is from 07:09 and it changes to 0 at 07:18 then my sustainability time should be 8 minutes. How can i acheive this? Please share ASAP.

[bhanu_gautam · Accepted solution]

@ZB_tech 

Create a new calculated column to calculate the time difference between each row. 

DAX
TimeDifference =
VAR CurrentTime = 'Table'[Time]
VAR PreviousTime =
CALCULATE(
MAX('Table'[Time]),
FILTER(
'Table',
'Table'[Time] < CurrentTime
)
)
RETURN
IF(
ISBLANK(PreviousTime),
0,
DATEDIFF(PreviousTime, CurrentTime, MINUTE)
)

[pankajnamekar25 · Accepted solution]

Sort the table by Time in ascending order.

Add an Index Column (called Index).

  Add a custom column to track when a new segment starts:

Go to Add Column > Custom Column, name it Segment Key,

if [Index] = 0 then 0

else if [Damper Position] = #"Previous Step"[Damper Position]{[Index]-1} then null

else [Index]

  Fill down the Segment Key column to assign segment start points across rows with the same damper position.

Group by Segment Key

Add columns

Min Time = Minimum of Time

Max Time = Maximum of Time

Damper Position = All Rows → Extract the first value from Damper Position

Add a custom column to calculate duration in minutes:

powerquery

Duration.Minutes([Max Time] - [Min Time])

 Remove unnecessary columns and rename as needed.

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[bhanu_gautam · Accepted solution]

@ZB_tech 

Create a new calculated column to calculate the time difference between each row. 

DAX
TimeDifference =
VAR CurrentTime = 'Table'[Time]
VAR PreviousTime =
CALCULATE(
MAX('Table'[Time]),
FILTER(
'Table',
'Table'[Time] < CurrentTime
)
)
RETURN
IF(
ISBLANK(PreviousTime),
0,
DATEDIFF(PreviousTime, CurrentTime, MINUTE)
)