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 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!