Difference between 2 rows with timestamps format according to a condition

[valegalvan]

Hi,

I've seen some examples of calculating differences between two rows, however can't find something similar to what I need. I have a table with different states and a timestamp for each one of them. What I need it's to calculate the time difference between 2 rows according to two conditions.

I will explain de table:

The field "Cama" is a bed location, The field "EstadoID" sets the state of the Field "Cama", each number of the field "EstadoID" represents a state of the bed for example EstadoID=1 is Ocupped and EstadoID =17 means that the cleaning process has started. For each change of state, the table registers a row with a timestamp of the moment that change of state occurs. In one day I can have multiple changes of states of the same "Cama" (bed). In particular, I need to calculate the difference of time between the "EstadoID"=1 and the "EstadoID"=2 of the Same "Cama" (bed).

In this example, the result of the calculation of change of "EstadoID" from 1 to 2 in the "Cama" (bed) 161 is 00:15:13. The problem is that in the same day I can have more than one change of the same "Cama" (bed) from "EstadoID"=1 to "EstadoID"=2

Can you help me to calculate this time difference?

[Anonymous · Accepted solution]

Hi @valegalvan ,

Refer:

Column = var _latest1 = CALCULATE(MAX('Table'[Fecha]),FILTER('Table','Table'[Cama]=EARLIER('Table'[Cama])&&'Table'[EstadoID]=1&&'Table'[Fecha]<EARLIER('Table'[Fecha])))
var _diff = DATEDIFF(_latest1,'Table'[Fecha],MINUTE)
return
IF('Table'[EstadoID]=2,_diff)

Best Regards,

Jay

[Greg_Deckler]

@valegalvan 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/3395....
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