Calculating time between despatching two orders

[Anonymous]

Hi!

I have a problem regarding my calculations. I have a despatch data, and I need to calculate how much time it takes from despatching one order until despatching another.

The data has these columns:
Sales Order Number - this is a unique values for each sales order.

Despatch date - this is the date (dd/mm/yyyy hh:mm) when order has been despatched.
Username - the worker that despatched the specific sales order. 

The data time frame is one month, so it has around 70k rows. Moreover, despatching process is quite fast, which means that one worker can despatch the more than one order within the same minute. 
I tried to sort my data and do another calculated column which would show earlier despatch date:
(Previous Despatch Date =

CALCULATE(

    MAX('Despatch summary (6)'[Despatch date]),

    FILTER(

        'Despatch summary (6)',

        'Despatch summary (6)'[Username] = EARLIER('Despatch summary (6)'[Username])

        && 'Despatch summary (6)'[Despatch date] < EARLIER('Despatch summary (6)'[Despatch date])

    ))
The formula works, but when worker despatches few orders in the same minute, it shows previous despatch date that is different, and when I am calculating time spent, it shows one minute, instead of 0.
This is what I am getting now:

Sales Order Number	Despatch date	Username	Previous despatch date
12555484	02/01/2023 07:15	Worker 1	02/01/2023 07:14
12574687	02/01/2023 07:16	Worker 1	02/01/2023 07:15
58745598	02/01/2023 07:16	Worker 1	02/01/2023 07:15
55545487	02/01/2023 07:16	Worker 1	02/01/2023 07:15

 This is how it should look like:

Sales Order Number	Despatch date	Username	Previous despatch date
12555484	02/01/2023 07:15	Worker 1	02/01/2023 07:14
12574687	02/01/2023 07:16	Worker 1	02/01/2023 07:15
58745598	02/01/2023 07:16	Worker 1	02/01/2023 07:16
55545487	02/01/2023 07:16	Worker 1	02/01/2023 07:16

The bolded date values shows the difference between what I am getting and what I should get.
Moreover, as the day and user changes in the data, the value in the column "Previous Despatch Date" in that case should return to blank.

Thank you in advance!

[Fowmy]

@Anonymous 

For DAX to find out the previous line, it has to appply sorting, since you have duplicate Despatch dates within each user, you need to include an additional column to have a unique record set, I added the order number in to this logic and you get the expected result. Add this as a column:

Previous = 
VAR __User = Table9[Username ]
VAR __T =  SUMMARIZE( ALLSELECTED( Table9 ) , Table9[Username ] , Table9[Despatch date] , Table9[Sales Order Number] )
RETURN
    MAXX(
        OFFSET( -1 , 
        __T , 
        ORDERBY(Table9[Username ],ASC,Table9[Despatch date],ASC,Table9[Sales Order Number],ASC ) , 
        PARTITIONBY( Table9[Username ] ) ) , Table9[Despatch date] 
    )

[Anonymous]

Thank you for your answer!
I used the formula, and I am getting this, which still seems not correct, some values are okay, some are not. You can see the formula, is there something I did wrong?

[Fowmy · Accepted solution]

@Anonymous 

As mentioned earlier, there are duplicate dispatch dates in your data. To address this, sorting by order number is essential. If you sort by order number after the delivery date, you should obtain the desired result. If using the order number is not preferable, kindly specify your preferred sorting criteria for the dates.

It seems you're anticipating the previous row to display as it appears on the screen, but it's crucial to note that DAX requires proper sorting.