Calculate duration from latest Date / time and Earlier date/time with multiple condition

[Anonymous]

Hello Community,

            I would like to calculate rest time for Driver   condition is rest time must be equal to or more than 10 hours.

but the issue is a data structure I retrieve from Database  here is an example. 

D_NAME	MT_NAME	VLCTL_GPS_DATE	Time
MR.JOHN CONNOR	Card Swipe In	09/10/2023	08:26:39
MR.JOHN CONNOR	Card Swipe In	09/10/2023	09:54:44
MR.JOHN CONNOR	Card Swipe Out	09/10/2023	09:04:35
MR.JOHN CONNOR	Card Swipe Out	09/10/2023	09:56:44
MR.JOHN CONNOR	Card Swipe In	07/10/2023	06:21:55
MR.JOHN CONNOR	Card Swipe In	07/10/2023	06:41:15
MR.JOHN CONNOR	Card Swipe In	07/10/2023	07:51:34
MR.JOHN CONNOR	Card Swipe In	07/10/2023	13:54:04
MR.JOHN CONNOR	Card Swipe In	07/10/2023	18:01:18
MR.JOHN CONNOR	Card Swipe Out	07/10/2023	06:33:30
MR.JOHN CONNOR	Card Swipe Out	07/10/2023	06:46:23
MR.JOHN CONNOR	Card Swipe Out	07/10/2023	08:53:29
MR.JOHN CONNOR	Card Swipe Out	07/10/2023	08:53:30
MR.JOHN CONNOR	Card Swipe Out	07/10/2023	14:00:05
MR.JOHN CONNOR	Card Swipe Out	07/10/2023	18:34:30
MR.JOHN CONNOR	Card Swipe In	06/10/2023	12:47:09
MR.JOHN CONNOR	Card Swipe In	06/10/2023	12:56:38
MR.JOHN CONNOR	Card Swipe In	06/10/2023	13:17:25
MR.JOHN CONNOR	Card Swipe Out	06/10/2023	12:49:46
MR.JOHN CONNOR	Card Swipe Out	06/10/2023	13:02:00
MR.JOHN CONNOR	Card Swipe Out	06/10/2023	13:20:22

As you can see there are many Swipe in and out in one day.

The conditoon to select what date and time for calculation is

1) lookup date and time  the latest "Card Swipe Out"  (Highlighted) on previous day and earliest date and time for "Card swipe In". date and time between Swipe In and Swipe out must be differ. Swipe out should be  Swipe In Date - 1 or 2, 3 .....
     [ex. John lastest data on 09/10/2023 the first "Card swipe In" at 08:26   and the lastest correct "Card swipe out" is on 07/10/2023 18:34  must exclude "Card swipe out" on 09/10/2023 because it come from he turn off the truck.]

2) compare 2 times  in hours format   
3) If 2 time different more than 10 hours  display " ENOUGH REST"  if not "NOT ENOUGH REST"

expect result 

D_NAME	LatestCardSwipeIn	LatestCardSwipeOut	Rest Duration (HRS)	RestStatus
MR.JOHN CONNOR	09/10/2023 08:26	07/10/2023 18:34	37	ENOUGH REST

I have attach pbix file for ref.

EXAMPLE 

is it possible to complete this in power BI or power query ?

Thank you

Best Regards,

JJ

[mickey64 · Accepted solution]

For your reference.

Step 1: I add a column 'Merged' and rename to 'Date_Time' in Power Query Editor.

Step 2: I make 3 measures below in Power BI Desktop.

    Latest In Date_Time = MAXX(FILTER('DATA','DATA'[MT_NAME]="Card Swipe In"),'DATA'[Date_Time])

    Latest Out Date_Time = MAXX(FILTER('DATA','DATA'[MT_NAME]="Card Swipe Out"&&'DATA'[VLCTL_GPS_DATE]<MAX('DATA'[VLCTL_GPS_DATE])),'DATA'[Date_Time])

    Duration Time hrs = DATEDIFF([Latest Out Date_Time],[Latest In Date_Time],HOUR)

Step 3: I make a matrix below.

[Anonymous]

I follow your step and it work. 

for Rest time condition 

i used : 

IF([Duration Time] >= TIME(10, 0, 0), "Sufficient Rest Time", "Insufficient Rest Time")

the result is all become "insufficient Rest time"  so i change to 

IF([Duration Time] >= 10, "Sufficient Rest Time", "Insufficient Rest Time")

and it work.

[mh2587]

//Create Calcualeted Columns
Rest Time = [End Time] - [Start Time]
Rest Time Condition = IF([Rest Time] >= TIME(10, 0, 0), "Sufficient Rest Time", "Insufficient Rest Time")

[Anonymous]

I cant create Calculated columns 
when i create this 
Rest Time = [Latest Out] - [Latest In]   
Measure Latest Out = 

MAXX(FILTER('VEHICLE_LOCATION_LOG_2023','VEHICLE_LOCATION_LOG_2023'[MT_NAME]="Card Swipe Out"&&'VEHICLE_LOCATION_LOG_2023'[VLCTL_GPS_DATE]<MAX('VEHICLE_LOCATION_LOG_2023'[VLCTL_GPS_DATE])),'VEHICLE_LOCATION_LOG_2023'[Date_Time])

Lastest In = 

MAXX(FILTER('VEHICLE_LOCATION_LOG_2023','VEHICLE_LOCATION_LOG_2023'[MT_NAME]="Card Swipe In"),'VEHICLE_LOCATION_LOG_2023'[Date_Time])

error:A circular dependency was detected:

anyway i find the result  thank you.