Forum Discussion

nleuck_101's avatar
nleuck_101
Icon for Continued Contributor rankContinued Contributor
3 years ago
Solved

Find if time is between 2 different times

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!

Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    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"
)

14 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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"
)
  • nleuck_101's avatar
    nleuck_101
    Icon for Continued Contributor rankContinued Contributor
    3 years ago

    parry2k 

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      3 years ago

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

    • nleuck_101's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      3 years ago
      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's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      3 years ago

      parry2k 

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

    • nleuck_101's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      3 years ago

      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's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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"
)