How to Find Orders Duration based on Start Stop time

[Wirestat]

Hello, how its possible to find order duration based on starting and finishing time, for example like in column duration. 

Order id	From	To	Duration
0	23:00	23:03
1306993	23:03	23:03
1306993	23:03	23:06
1306993	23:22	23:33
1306993	23:33	23:33
1306993	23:34	23:35
1306993	23:35	23:37
1306993	23:38	23:39
1306993	23:42	23:43
1306993	23:43	23:43	00:40
0	23:43	23:44
1305300	23:44	23:45
1305300	23:45	23:46
1305300	23:47	23:47
1305300	23:48	23:49
1305300	23:54	23:54
1305300	23:54	23:55
1305300	23:56	23:59
1305300	23:59	00:00
1305300	00:00	00:01
1305300	00:02	00:03
1305300	00:03	00:03
1305300	00:04	00:04
1305300	00:04	00:07
1305300	00:07	00:07
1305300	00:07	00:08
1305300	00:08	00:09	00:25
0	00:09	00:10
1307012	00:53	00:54
1307012	00:54	00:55
1307012	00:56	00:58
1307012	01:02	01:05	00:12
0	01:05	01:05
1307031	01:05	01:05
1307031	01:06	01:06
1307031	01:11	01:11
1307031	01:13	01:14
1307031	01:19	01:42
1307031	01:44	01:45
1307031	01:45	01:46
1307031	01:46	01:46	00:41

[FreemanZ]

hi @Wirestat 

try to plot a table visual  with the order id column and a measure like:

Duration = 
MAXX(
    TableName,
    TableName[To]            
)
-
MINX(
    TableName,
    TableName[From]
)

it worked like:

your from and to columns shall include date info, with date info, you shall get the correct duration for 1305300 as well. 

[tamerj1]

 

Hi @Wirestat 

what about the gaps?

[Wirestat]

Hello tamerj1, the gaps shows sub orders serving, just ignore them, the goal to display duration of order from 23:03 until 23:43 which is 40 min time for order 1306993

[tamerj1 · Accepted solution]

Hi @Wirestat 
Please try

Duration (Minutes) =
SUMX (
    VALUES ( 'Table'[Order ID] ),
    VAR CurrentIDTable =
        CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) )
    VAR FirstStart =
        MINX ( CurrentIDTable, 'Table'[From] )
    VAR LastEnd =
        MAXX ( CurrentIDTable, 'Table'[To] )
    RETURN
        DATEDIFF ( FirstStart, LastEnd, MINUTE )
)

[Wirestat]

Thank you sir, its working!

[Anonymous]

Hi. 
Use DateDifference function to achieve the same requirement.

Minutes taken = DATEDIFF(tablename[From], tablename[To], MINUTE)

Though this will just give the minutes. 

To achieve the exact requirement, you can follow the Solution mentioned in this below link:
Solved: datediff in HH:MM format - Microsoft Power BI Community
OR
Aggregating Duration/Time - Microsoft Power BI Community

Thank you.
If my answer helps you, please mark it as solution.

[Wirestat]

Hi VishalJhaveri, unfortunatly this answer provide diffrance for each row, and my goal is to find duration for complete order. Thank your for affort!