Subtract same column (time data) based on 3 criteria

[sniakarou]

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!!

[sniakarou]

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]

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 _ Eason

[sniakarou]

Hi @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?

[sniakarou]

@v-easonf-msft To give you more context, while Truck A might operate the first route to Store A, a Truck B can visit the Store B at a later time. Does this help?

[v-easonf-msft]

Hi, @sniakarou 

Not quite sure. Can you show your expected result in a table?

Best Regards,
Community Support Team _ Eason

[sniakarou]

Hi @v-easonf-msft !

After all you formula worked - I cleaned up most of the database in Excel cause I could not find any other solution!

However, I need your help in something if you can please!

Case Scenario

Same Vehicle ID + Same Store ID + Same Date

However, I might have three routes per day - so how can I incorporate this to a formula?

So, for example:

Date	Time	Vehicle ID	Store ID	Expected Result
13/1/2021	08:00	BS691	564	First Route
13/1/2021	10:00	BS691	564	Second Route
13/1/2021	12:00	BS691	564	Third Route (or Second if that is not possible)

I am a bit confused with the earlier dax that is why I am asking for you help!

Also, I wanted to ask - that part of your formula what is exactly that it does? 

I need to see if I can edit it somehow to ignore the duplicates because of the GPS problem I mentioned earlier!

 && Routes[Departure Time]
                    >= EARLIER ( Routes[Departure Time] ) - TIME ( 0, 30, 0 )
                && Routes[Departure Time] < EARLIER ( Routes[Departure Time] )
        )
    ) + 0

Thanks a lot in advance!!

[sniakarou]

Date	Time	Vehicle ID	Store ID	DAX
13/1/21	08:00	BS691	564	First Route
13/1/21	08:15	BS691	564	Second Route
13/1/21	12:30	BS392	564	First Route

So basically @v-easonf-msft !

• The first and second lines is the same route but due to the problem GPS software, 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. 
• in the last line, there is a different vehicle ID and the same store ID at a different time - I want this to be cosidered as a second route. 

Is this clearer? I can elaborate more if you want!

[sniakarou]

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.

[sniakarou]

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]

You want the max and minimum value of dates?

[MFelix]

Hi @sniakarou ,

You want to subtract from what value? in this case the two lines? If you have more than one line how do you handle it?