Forum Discussion

nataliesmiy1357's avatar
nataliesmiy1357
Helper IV
2 years ago
Solved

DAX Formula - Time

Hello!  I am looking to create a formula that's if this and that, then this with time.

 

I want:  If the time is between 6am-5:10pm, then "First Shift"

          and  if the time is between 5:25pm - 5:55am, then "Second Shift"

 

Thanks in advance! 

Need Help
Show and Tell
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi nataliesmiy1357 - create below calculated column in your table, replace your Datetime column as per your model.

     

    Shift Classification =
    VAR CurrentTime = [DateTime]  
    VAR TimeFirstShiftStart = TIME(6, 0, 0)   -- 6:00 AM
    VAR TimeFirstShiftEnd = TIME(17, 10, 0)   -- 5:10 PM
    VAR TimeSecondShiftStart = TIME(17, 25, 0)  -- 5:25 PM
    VAR TimeSecondShiftEnd = TIME(5, 55, 0)  -- 5:55 AM (next day)

    RETURN
    IF (
        (CurrentTime >= TimeFirstShiftStart && CurrentTime <= TimeFirstShiftEnd) ||
        (CurrentTime >= TimeSecondShiftStart || CurrentTime <= TimeSecondShiftEnd),
        "First Shift",
        "Second Shift"
    )

     

     

    Hope it helps

     

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi nataliesmiy1357 - create below calculated column in your table, replace your Datetime column as per your model.

     

    Shift Classification =
    VAR CurrentTime = [DateTime]  
    VAR TimeFirstShiftStart = TIME(6, 0, 0)   -- 6:00 AM
    VAR TimeFirstShiftEnd = TIME(17, 10, 0)   -- 5:10 PM
    VAR TimeSecondShiftStart = TIME(17, 25, 0)  -- 5:25 PM
    VAR TimeSecondShiftEnd = TIME(5, 55, 0)  -- 5:55 AM (next day)

    RETURN
    IF (
        (CurrentTime >= TimeFirstShiftStart && CurrentTime <= TimeFirstShiftEnd) ||
        (CurrentTime >= TimeSecondShiftStart || CurrentTime <= TimeSecondShiftEnd),
        "First Shift",
        "Second Shift"
    )

     

     

    Hope it helps