Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
thank you, i've tried this with no luck , it did not result like i described above
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
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:
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 |
Aha, it is easier in this case:
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 !!