Creating Shifts based on Date and Time

[Anonymous]

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" = 9pm on 03/21/2022 to 6am on 03/22/2022

"PM Dispatch Wave" = 7am on 03/22/2022 to 3pm on 03/22/2022

I am using the following syntax to break the shifts:

SHIFT = 
VAR hour =
    HOUR ( OperatorTTP[SESSION_START_TIME_LOCAL] )
VAR MINUTE =
    MINUTE ( OperatorTTP[SESSION_START_TIME_LOCAL] )
VAR test =
    FORMAT ( OperatorTTP[SESSION_START_TIME_LOCAL], "hh:mm:ss" )
RETURN
    IF (
        test >= "07:00:00"
            && test < "21:00:00",
        "PM Dispatch Wave",
         IF ( test >= "21:00:00" && test < "06:00:00", "What is this?", "AM Dispatch Wave" )
    )

The above code has been adopted from reading various posts here.

Here is the screenshot of my slicer filter:

This brings in all the AM Dispatch shifts for the selected date range. So, if I have selected date range as 03/21/2022 - 03/22/2022, then the results I see have data from AM Dispatch for 03/21/2022 and 03/22/2022. 

What I want to achieve is the AM Dispatch shift which starts at 9pm on 03/21/2022 and ends at 6am on 03/22/2022.

*** EDIT: The column "SESSION_START_TIME_LOCAL" contains both Date and Time of the transaction and is also formatted as such.

Thank you for your help!

[Anonymous]

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 · Accepted solution]

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]

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!

[amitchandak]

@Anonymous , Based on what I got

A new column

Switch( True() ,

timevalue([SESSION_START_TIME_LOCAL]) >= (21,0,0) || timevalue([SESSION_START_TIME_LOCAL]) < (6,0,0), "AM Dispatch Wave" , PM Dispatch Wave)