cancel
Showing results 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

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

1 ACCEPTED SOLUTION
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))``````

Proud to be a Super User!

3 REPLIES 3
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))``````

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.

Super User

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

Proud to be a Super User!

Announcements

#### 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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors