Find if time is between 2 different times

[nleuck_101]

Hello All,

I have a Time table that I calculated with the following DAX:

I would like to add a Shift column the states if the time is between these two times it's 1st shift. Or if the time is between these other two times it's 2nd shift. If it's not either of those then there is no shift. Below is the code I'm using:

It correctly gets the first shift but does not get second shift at all.

Any help would be greatly appreciated.

Thanks!

[parry2k · Accepted solution]

@nleuck_101 this:

Shift = 
SWITCH ( TRUE (),
  'Time'[TimeandDay] >= TIME ( 8,0,0) &&   'Time'[TimeandDay] < TIME(16,30,0), "Shift 1",
  ( 'Time'[TimeandDay] >= TIME ( 18,30,0) ) || ( 'Time'[TimeAndDay] >= TIME(0,0,0) && 'Time'[TimeAndDay] < TIME (3, 0,0)), "Shift 2",
  "Shift 0"
)

[nleuck_101]

@parry2k 

Thank you that worked!

[parry2k]

@nleuck_101 I have no idea what you are talking about, give the range in plain simple English (NO DAX)

[nleuck_101]

@parry2k 

1st shift = 8:00am to 4:30pm
2nd shift = 6:30pm to 3:00am

[parry2k]

@nleuck_101 See attached, in my opinion, it is working

[nleuck_101]

@parry2k 
But 2nd shift cross over into midnight when the time changes. If I use just use >= TIME(18, 30, 0) at midnight it flips back to 0. 2nd shift needs to go through to 3:00am.

[parry2k]

@nleuck_101 I think you need this:

SWITCH ( TRUE (),
  'Time'[TimeandDay] >= TIME ( 3,0,0) &&   'Time'[TimeandDay] < TIME(8,0,0), "Shift 0",
  'Time'[TimeandDay] >= TIME ( 8,0,0) &&   'Time'[TimeandDay] < TIME(16,30,0), "Shift 1",
  'Time'[TimeandDay] >= TIME ( 18,30,0), "Shift 2",
  "Shift 0"
)

[nleuck_101]

@parry2k 

Still doesn't work. I only get 1st shift. Everything else is 0.

[parry2k]

@nleuck_101 can you give me the range of your shifts:

[nleuck_101]

SWITCH(

    TRUE(),

    'Time'[TimeandDay] >= TIME(8, 0, 0) && 'Time'[TimeandDay] <= TIME(16, 30, 0), 1,

    'Time'[TimeandDay] >= TIME(18, 30, 0) && 'Time'[TimeandDay] <= TIME(3, 0, 0), 2,

    0

)

[parry2k]

@nleuck_101 try this:

SWITCH ( TRUE ()
Table[TimeAndDay] >= TIME ( 8,0,0) && Table[TimeAnDay] < TIME(16,0,0), "Shift 1",
Table[TimeAndDay] >= TIME ( 16,0,0) && Table[TimeAnDay] <= TIME(23,59,0), "Shift 2",
"Shift 3"
)

[nleuck_101]

@parry2k 
I've tried changing the times around for 2nd shift and it doesn't work.

[nleuck_101]

@parry2k 

It's not working. I'm still on getting 1st Shift and 3rd Shift. It's not picking up the 2nd Shift.

[parry2k]

@nleuck_101 you should compare it against timeandday column which is actually time rather than a text column:

SWITCH ( TRUE ()
Table[TimeAndDay] >= TIME ( 8,0,0) && Table[TimeAnDay] < TIME(16,0,0), "Shift 1",
Table[TimeAndDay] >= TIME ( 16,0,0) && Table[TimeAnDay] < TIME(0,0,0), "Shift 2",
"Shift 3"
)