Forum Discussion
Subtract same column (time data) based on 3 criteria
Hi everyone!
I must say that the community of power bi has helped me solved a lot of problems I encountered!
This is a new one that I really need to solve!
I have a huge data table with the following details:
| Date | Time of departure | Store ID | Vehicle License Plates |
| 13/1/2022 | 09:30 | 352 | BS 691 |
| 13/1/2022 | 09:45 | 352 | S 691 |
I need to subtract column "Time of Departure" if the row fields "Date", "Store ID" and "Vehicle License Plates" are the same and create a new column to calculate the time difference in order to count the visit to the store as 1 in report view if the time difference is less than 30 minutes.
Any ideas?
All the help will be appreciated!
Thank you so much!!
12 Replies
If I can give any more information, please do tell me!
I have included here a table that might help 🙂
Departure Date Departure Time Vehicle ID KM Driver Store ID 6/3/21 7:20 BS 691 6.08 Egerton 134 6/3/21 7:32 BS 691 0.06 Egerton 564 6/3/21 7:42 BS 691 1.33 Egerton 564 6/3/21 8:27 BS 691 30.72 Egerton 141 6/3/21 9:19 BS 691 6.85 Egerton 3 6/3/21 9:54 BS 691 3.85 Egerton 132 6/3/21 10:27 BS 691 0.00 Egerton 4 6/3/21 10:33 BS 691 4.51 Egerton 4 6/3/21 10:51 BS 691 0.03 Egerton 117 6/3/21 10:58 BS 691 5.59 Egerton 117 6/3/21 11:26 BS 691 33.62 Egerton 121 - v-easonf-msft
Community Support
Hi, sniakarou
Is this what you want?
Result = VAR _count = CALCULATE ( COUNTROWS ( Routes ), FILTER ( Routes, Routes[Departure Date] = EARLIER ( Routes[Departure Date] ) && Routes[Vehicle ID] = EARLIER ( Routes[Vehicle ID] ) && Routes[Store ID] = EARLIER ( Routes[Store ID] ) && Routes[Departure Time] >= EARLIER ( Routes[Departure Time] ) - TIME ( 0, 30, 0 ) && Routes[Departure Time] < EARLIER ( Routes[Departure Time] ) ) ) + 0 RETURN IF ( _count = 0, "First Route", "Second Route" )Best Regards,
Community Support Team _ EasonHi @v-easonf-msft ,
Thank you for your help! I tried it it worked actually! The only problem is that in case there are three records with the same vehicle id plates it gets complicated - it shows the 13/1 08:00 as first route, the 13/1 08:30 as second route and the 13/1 09:00 as first route... Any ideas of how can I solve this?
Hi there, thank you so much for your reply!
Your question is very to the point... the problem is that I have a very big database with the routes of the trucks from the entire year - every truck arrives at X stores every day, but the GPS routing software (most of the times) shows the arrival more than 3 times at the same store with 3 to 20 minutes time difference as the unloading area might not be free - so the truck has to wait and when it shuts off the engine and restarts it the routing software creates a new record.
The idea is to find the "duplicates" and subtract one value from the same column if it has identical records.
So basically, if date, store id and vehicle plates have two or more records, ideally to subtract 09:45 from 09:30 and if the difference is up to 30 minutes to count it as 1 record.
Date Time of departure Store ID Vehicle License Plates 13/1/2022 09:30 352 BS 691 13/1/2022 09:45 352 BS 691 I believe this is not feasible but I thought I could ask for help just in case...
The other solution I thought of was to create a calculated column, with this formula, in order to count the first and second occurence of routes and create visuals in report view to filter out errors.
Occurrence =
IF(COUNTROWS
(FILTER(Routes, Routes[Store ID] = Routes[ID] && Routes[Vehicle ID] =Routes[Vehicle ID] &&
Routes[Date Time] =EARLIER( _Routes[Date Time]))
) = 1,
"First Route","Second Route")Any help will be greatly appreciated!
Thank you so much!
- MFelix
Super User
You want the max and minimum value of dates?
That could work too if I can create a calculated column where the last instance of previous route could show as a result based on the filters same store id, date and vehicle id.
I get it is too complicated...
- MFelix
Not complicated just want to understand the data.
