Find Order Duration based on Start Stop time

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