Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Time diff between stages

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 NumberData     indexSupporting Columnindex1Time between stages (seconds)
GD-14.6620.5.3.0051.15.202001.02.2021 09:18:4911100:00:00
GD-14.6620.5.3.0051.15.202001.02.2021 09:18:5521200:00:00
GD-14.6620.5.3.0051.15.202001.02.2021 09:20:4131300:00:00
GD-14.6620.5.3.0051.15.202001.02.2021 09:20:4841400:00:00
GD-14.6620.5.3.0051.15.202001.02.2021 09:22:5051500:00:00
P.1261.2021.100701.02.2021 13:47:5866100:00:00
P.1261.2021.100701.02.2021 13:48:1576200:00:00
P.1261.2021.100702.02.2021 09:31:5886300:00:00
P.1261.2021.100703.02.2021 14:14:2796400:00:00
P.1261.2021.100718.02.2021 13:20:39106500:00:00
GD-10.6621.1271.202113.05.2021 14:05:071111100:00:00
GD-10.6621.1271.202113.05.2021 14:05:091211200:00:00
GD-10.6621.1271.202113.05.2021 14:05:141311300:00:00

 

 

and picture:

 



Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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))

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        ryan_mayu
        Super User
        5 years ago

        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))