Find Order Duration based on Start Stop time

[Wirestat]

Hello, please can you help to find order duration based on starting and finishing time 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

[tamerj1]

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

[v-yangliu-msft]

Hi  @Wirestat ,

Here are the steps you can follow：

1. . In Power Query -- Create Indices for the two tables -- Add Column – Index Column – From 1.

2. Create calculated column.

Rank =
RANKX(
    FILTER(ALL('Table'),'Table'[Order id]=EARLIER('Table'[Order id])),[Index],,ASC)

Flag =
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order id] = EARLIER ( 'Table'[Order id] )
                && 'Table'[Rank]
                    = MINX (
                        FILTER ( ALL ( 'Table' ), 'Table'[Order id] = EARLIER ( 'Table'[Order id] ) ),
                        [Rank]
                    )
        ),
        [From]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order id] = EARLIER ( 'Table'[Order id] )
                && 'Table'[Rank]
                    = MAXX (
                        FILTER ( ALL ( 'Table' ), 'Table'[Order id] = EARLIER ( 'Table'[Order id] ) ),
                        [Rank]
                    )
        ),
        [To]
    )
VAR _datediff =
    DATEDIFF ( _mindate, _maxdate, MINUTE )
RETURN
    IF (
        'Table'[Order id] = 0,
        BLANK (),
        IF (
            VALUE ( LEFT ( _mindate, FIND ( ":", _mindate ) - 1 ) )
                < VALUE ( LEFT ( _maxdate, FIND ( ":", _maxdate ) - 1 ) ),
            _datediff + 24 * 60,
            _datediff
        )
    )

Time =
var _hour=
QUOTIENT('Table'[Flag],60)
var _minute=
MOD('Table'[Flag],60)
return
IF(
    [Flag]=BLANK(),BLANK(),
IF(
    'Table'[Rank]=
    MAXX(FILTER(ALL('Table'),'Table'[Order id]=EARLIER('Table'[Order id])),[Rank]),
TIME(
    _hour,_minute,0),BLANK()))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Wirestat]

Hello @v-yangliu-msft 

Thank you but i get an error