topic Re: how do i get the previous id for each order ID in DAX Commands and Tips

how do i get the previous id for each order ID

Shakerpowerbi — Sat, 17 Dec 2022 08:02:52 GMT

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

Re: how do i get the previous id for each order ID

Mahesh0016 — Sat, 17 Dec 2022 08:57:53 GMT

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

HelloShakerpowerbi ,

i hope this post helps.

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

Re: how do i get the previous id for each order ID

Shakerpowerbi — Sat, 17 Dec 2022 09:55:49 GMT

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

Re: how do i get the previous id for each order ID

Mahesh0016 — Sat, 17 Dec 2022 09:56:51 GMT

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

Re: how do i get the previous id for each order ID

Shakerpowerbi — Sat, 17 Dec 2022 10:09:19 GMT

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

Re: how do i get the previous id for each order ID

Shakerpowerbi — Sat, 17 Dec 2022 10:10:08 GMT

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

Re: how do i get the previous id for each order ID

FreemanZ — Sat, 17 Dec 2022 11:54:42 GMT

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:

Re: how do i get the previous id for each order ID

FreemanZ — Sat, 17 Dec 2022 11:55:59 GMT

Aha, it is easier in this case: