Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |