Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

Venqui_0-1630068799360.png

 



1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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. 

 

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.