Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating Shifts based on Date and Time

Hi All:   I am trying to filter some performance data based on date and time. Here are the shift examples:   Example is based on 03/21/2022 9pm - 03/22/2022 5pm working day.   "AM Dispatch Wave...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    You can update the formula of your calculated column [SHIFT] as below:

    SHIFT = 
VAR test =
    TIME ( HOUR ( 'OperatorTTP'[SESSION_START_TIME_LOCAL] ), MINUTE ( 'OperatorTTP'[SESSION_START_TIME_LOCAL] ), SECOND ( 'OperatorTTP'[SESSION_START_TIME_LOCAL] ) )
RETURN
    IF (
        test >= TIME ( 7, 0, 0 )
            && test < TIME ( 15, 0, 0 ),
        "PM Dispatch Wave",
        IF (
            (
                test >= TIME ( 0, 0, 0 )
                    && test < TIME ( 6, 0, 0 )
                    || test >= TIME ( 21, 0, 0 )
                        && test < TIME ( 23, 59, 59 )
            ),
            "AM Dispatch Wave",
            "What is this?"
        )
    )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi amitchandak!

 

Thank you for your quick response. I tried the syntax you posted, but I got an error. Here is the screenshot of the error:

 

Can you please advise?

 

Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    You can update the formula of your calculated column [SHIFT] as below:

    SHIFT = 
VAR test =
    TIME ( HOUR ( 'OperatorTTP'[SESSION_START_TIME_LOCAL] ), MINUTE ( 'OperatorTTP'[SESSION_START_TIME_LOCAL] ), SECOND ( 'OperatorTTP'[SESSION_START_TIME_LOCAL] ) )
RETURN
    IF (
        test >= TIME ( 7, 0, 0 )
            && test < TIME ( 15, 0, 0 ),
        "PM Dispatch Wave",
        IF (
            (
                test >= TIME ( 0, 0, 0 )
                    && test < TIME ( 6, 0, 0 )
                    || test >= TIME ( 21, 0, 0 )
                        && test < TIME ( 23, 59, 59 )
            ),
            "AM Dispatch Wave",
            "What is this?"
        )
    )

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous !

       

      Thank you for the code! Apologies for late response. I am moving between states and could not check your reply sooner.

       

      In my code, I had used "What is This?" as a way to catch errors where the transactions are not allocated a shift. Essentially, if the code works, then we shouldn't have "What is this?" value.

       

      The crucial thing to note here is the change in date in the middle of the shift.

       

      Can you please help me with this? 

      Thanks!