Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sniakarou
Frequent Visitor

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:

 

DateTime of departureStore IDVehicle License Plates
13/1/202209:30352BS 691
13/1/202209:45352S 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 12
sniakarou
Frequent Visitor

If I can give any more information, please do tell me!

 

I have included here a table that might help 🙂

 

Departure DateDeparture TimeVehicle IDKMDriverStore ID
6/3/217:20BS 6916.08Egerton134
6/3/217:32BS 6910.06Egerton564
6/3/217:42BS 6911.33Egerton564
6/3/218:27BS 69130.72Egerton141
6/3/219:19BS 6916.85Egerton3
6/3/219:54BS 6913.85Egerton132
6/3/2110:27BS 6910.00Egerton4
6/3/2110:33BS 6914.51Egerton4
6/3/2110:51BS 6910.03Egerton117
6/3/2110:58BS 6915.59Egerton117
6/3/2111:26BS 69133.62Egerton121

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

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?

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

Hi, @sniakarou 

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

 

Best Regards,
Community Support Team _ Eason

 

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:

 

DateTimeVehicle IDStore IDExpected Result
13/1/202108:00BS691564First Route
13/1/202110:00BS691564Second Route
13/1/202112:00BS691564Third 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!!

 

DateTimeVehicle IDStore IDDAX
13/1/2108:00BS691564

First Route

13/1/2108:15BS691564Second Route
13/1/2112:30BS392564First 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
Frequent Visitor

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

Not complicated just want to understand the data.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



sniakarou
Frequent Visitor

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. 

 
DateTime of departureStore IDVehicle License Plates
13/1/202209:30352BS 691
13/1/202209:45352BS 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?

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.