Help finding earliest and lates times in different columns with criteria

[gavinf4444]

Hi, I've seen some solutions where users have looked for the earliest dates/times and have used the Group By / Min function to achieve this however I have a slightly more complicated situation which I cannot get my head around.

I have 2 situations where I want to calculate the shift time of a machine with example data below.

In the first instance, I want to calculate the duration of the day shift (day shift = 06:00-17:59) so for Machine A this would be between 06:00 and 17:30 (690 minutes)

For night shifts (night shift = 18:00-05:59 following day) I think that it gets more complicated because the End Time could be the following day to the Start Date.  For Machine B I want to show 18:30-00:10 (280 minutes) and Machine C should be 22:00-23:45 (105 minutes).  Machine would be 30 minutes however I'd want it to appear with a shift date of 07/07/23.

Desired Output -

Machine	Shift Start Date	Shift	Duration
A	07/07/23	Day	690
B	07/07/23	Night	280
C	07/07/23	Night	105
D	07/07/23	Night	30

Sample Data - 

Machine	Start Date	Start Time	End Date	End Time
A	07/07/23	06:00	07/07/23	12:00
A	07/07/23	13:00	07/07/23	15:00
A	07/07/23	16:00	07/07/23	17:30
B	07/07/23	18:30	07/07/23	19:00
B	07/07/23	23:00	08/07/23 (UK format)	00:10
C	07/07/23	22:00	07/07/23	22:30
C	07/07/23	23:30	07/07/23	23:45
D	08/07/23 (UK format)	00:00	08/07/23	00:30

[Anonymous · Accepted solution]

Hi @gavinf4444 

You can refer to the following solutions

1.Create four calculated columns in table

StartTimeDate = CONVERT([Start Date]&" "&[Start Time],DATETIME)

EndTimeDate = CONVERT([End Date]&" "&[End Time],DATETIME)

ShitDate = IF([Start Time]=TIMEVALUE("00:00"),[Start Date]-1,[Start Date]) 

Type =
VAR a =
    CONVERT ( 'Table'[ShitDate] & " " & TIMEVALUE ( " 06:00" ), DATETIME )
VAR b =
    CONVERT ( 'Table'[ShitDate] & " " & TIMEVALUE ( "17:30" ), DATETIME )
VAR c =
    CONVERT ( 'Table'[ShitDate] & " " & TIMEVALUE ( "18:00" ), DATETIME )
VAR d =
    CONVERT ( 'Table'[ShitDate] + 1 & " " & TIMEVALUE ( "05:59" ), DATETIME )
RETURN
    SWITCH (
        TRUE (),
        [StartTimeDate] >= a
            && [EndTimeDate] <= b, "Day",
        [StartTimeDate] >= c
            && [EndTimeDate] <= d, "Night"
    )

2.Then create a measure

Duration =
VAR a =
    MINX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Machine]
                IN VALUES ( 'Table'[Machine] )
                    && [Type] IN VALUES ( 'Table'[Type] )
        ),
        [StartTimeDate]
    )
VAR b =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Machine]
                IN VALUES ( 'Table'[Machine] )
                    && [Type] IN VALUES ( 'Table'[Type] )
        ),
        [EndTimeDate]
    )
RETURN
    DATEDIFF ( a, b, MINUTE )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.