how do i get the previous id for each order ID

[Shakerpowerbi]

Hello luvs,

I’m trying to bullied a certain formula to help to get the previous order id, 

This must be done for each driver id and each day. 

the trick is for each driver id, the first order of the day must return the same id of that order, the second order of the day must return the first order id, third must return the second id ... etc. Until the all order of the same day are completed.

Please see below example, what i need to calculate is column (Previous order ID in same day) 

Thank you in advanced

[Shakerpowerbi]

thank you, i've tried this with no luck , it did not result like i described above 

[Mahesh0016]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Shakerpowerbi]

Data Table
Order id	Date of Delivery	Driver id	Output
53454	12/10/22 9:34 AM	11	53454
34534	12/10/22 10:30 AM	11	53454
35345	12/10/22 10:50 AM	11	34534
35345	12/10/22 11:30 AM	11	35345
65467	12/12/22 9:34 AM	11	65467
45745	12/12/22 10:30 AM	11	65467
45745	12/12/22 10:50 AM	11	45745
45745	12/12/22 11:30 AM	11	45745
47457	12/10/22 9:34 AM	33	47457
45774	12/10/22 10:30 AM	33	47457
47547	12/10/22 10:50 AM	33	45774
45754	12/10/22 11:30 AM	33	47547
45645	12/12/22 9:34 AM	33	45645
45645	12/12/22 10:30 AM	33	45645
45644	12/12/22 10:50 AM	33	45645
46456	12/12/22 11:30 AM	33	45644

like above, i need to get the order id for the previues nearest delivered order by same driver on the same day

[FreemanZ]

hi @Shakerpowerbi 

It would be easier with an Index Column. 

1) add an Index Column in Power Query

2) add a column with this:

Output2 = 
VAR _driverid = [DriverID]
VAR _date = INT([Date])
VAR _index =[Index]
VAR _dmin =
MINX(
    FILTER(
        tbl,
        _driverid = [DriverID]&&_date = INT([Date])
    ),
    tbl[Date]
)
VAR _OrderID =
MINX(
    FILTER(
        tbl,
        _driverid = [DriverID]&&_date = INT([Date])&&_index -1 =[Index]
    ),
    tbl[OrderID]
)
RETURN
IF(
    [Date] =_dmin,
    [OrderID],
    _OrderID
)

i tried and it worked like this:

[Shakerpowerbi]

Data Table
Order id	Date of Delivery	Driver id	Output
53454	12/10/22 9:34 AM	11	0
34534	12/10/22 10:30 AM	11	53454
35345	12/10/22 10:50 AM	11	34534
35345	12/10/22 11:30 AM	11	35345
65467	12/12/22 9:34 AM	11	0
45745	12/12/22 10:30 AM	11	65467
45745	12/12/22 10:50 AM	11	45745
45745	12/12/22 11:30 AM	11	45745
47457	12/10/22 9:34 AM	33	0
45774	12/10/22 10:30 AM	33	47457
47547	12/10/22 10:50 AM	33	45774
45754	12/10/22 11:30 AM	33	47547
45645	12/12/22 9:34 AM	33	0
45645	12/12/22 10:30 AM	33	45645
45644	12/12/22 10:50 AM	33	45645
46456	12/12/22 11:30 AM	33	45644

[FreemanZ · Accepted solution]

Aha, it is easier in this case:

[Mahesh0016]

CALCULATE(SUM(Order[Driver Id]), OFFSET(-1,ALLSELECTED(Order[Date of Delivery]),ORDERBY(Order[Date of Delivery],ASC)))

Use Measure As a Don't summarize

Hello@Shakerpowerbi ,

i hope this post helps.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!