Time diff between stages

[Anonymous]

Hi!

I have a poblem with proper calculate time between stages. I give you a sample of data. I have in this case three different orders (first column). I want to calculate proper time between stages (index1). For example index1 = 5 substract index1 = 4 but for the same order. Supporting column indicate the same order.  So for the  firstr order it will be:

1) (index1 = 5 (01.02.2021 09:22:50) and supporting column = 1) - (index1 = 4 (01.02.2021 09:20:48) and supporting column = 1)
2) (index1 = 4 (01.02.2021 09:20:48) and supporting column = 1) - (index1 = 3 (01.02.2021 09:20:41) and supporting column = 1)
and so on

I want to obtain the proper time in column named:

Time between stages (seconds)

How to solve this?

I wanted use smth with filter "EARLIER" FILTER(Orders,Orders[Supporting Column]=EARLIER(Orders[Supporting Column])

Sample of data:

Order Number	Data	index	Supporting Column	index1	Time between stages (seconds)
GD-14.6620.5.3.0051.15.2020	01.02.2021 09:18:49	1	1	1	00:00:00
GD-14.6620.5.3.0051.15.2020	01.02.2021 09:18:55	2	1	2	00:00:00
GD-14.6620.5.3.0051.15.2020	01.02.2021 09:20:41	3	1	3	00:00:00
GD-14.6620.5.3.0051.15.2020	01.02.2021 09:20:48	4	1	4	00:00:00
GD-14.6620.5.3.0051.15.2020	01.02.2021 09:22:50	5	1	5	00:00:00
P.1261.2021.1007	01.02.2021 13:47:58	6	6	1	00:00:00
P.1261.2021.1007	01.02.2021 13:48:15	7	6	2	00:00:00
P.1261.2021.1007	02.02.2021 09:31:58	8	6	3	00:00:00
P.1261.2021.1007	03.02.2021 14:14:27	9	6	4	00:00:00
P.1261.2021.1007	18.02.2021 13:20:39	10	6	5	00:00:00
GD-10.6621.1271.2021	13.05.2021 14:05:07	11	11	1	00:00:00
GD-10.6621.1271.2021	13.05.2021 14:05:09	12	11	2	00:00:00
GD-10.6621.1271.2021	13.05.2021 14:05:14	13	11	3	00:00:00

and picture:

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Column = 
VAR _last=maxx(FILTER('Table','Table'[Supporting Column]=EARLIER('Table'[Supporting Column])&&'Table'[index1]=EARLIER('Table'[index1])-1),'Table'[Data])
return if('Table'[index1]=1,blank(),DATEDIFF(_last,'Table'[Data],SECOND))

[Anonymous]

Yeah! It works!  Thank you, very much ! I appreciate your time @ryan_mayu.  Last question from me. It is possible to change value position - one row lower ? I mean (Max index1) -> blank() and next DATEDIFF till index1 = 1 with value in the same row. 

[ryan_mayu]

@Anonymous 

pls try this

Column = 
VAR _next=minx(FILTER('Table','Table'[Supporting Column]=EARLIER('Table'[Supporting Column])&&'Table'[index1]=EARLIER('Table'[index1])+1),'Table'[Data])
return if('Table'[index1]=CALCULATE(max('Table'[index1]),ALLEXCEPT('Table','Table'[Supporting Column])),blank(),DATEDIFF('Table'[Data],_next,SECOND))