Forum Discussion
Subtract same column (time data) based on 3 criteria
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!
You want the max and minimum value of dates?