DAX for identifying shifts

[Anonymous]

I have a timestamp label column (mm/dd/yyyy h:mm:ss) that I need to use to identify what shift it took place. I am connected to a server and cannot adjust the timestamp format or create columns. I need to create a measure to label the shifts. The shifts are
1a:  7:00am - 10:20am
2a: 10:20am - 1:30pm
3a:  1:30pm - 4:50pm
4a:  4:50pm - 7:00pm
1b:  7:00pm - 10:20pm
2b: 10:20pm - 1:30am
3b:  1:30am - 4:50am
4b:  4:50am - 7:00am

I've seen similar questions asked here, but they all seem to be for adding a column and not a measure. Thanks in advance. 

[ppm1]

You can either use a disconnected table or a SWITCH(TRUE() measure like the one below to do that. For vDT, use your expression to get the datetime value from your column.

Shift =
VAR vDT =
    MAX ( T2[DT] )
VAR vThisTime =
    TIME ( HOUR ( vDT ), MINUTE ( vDT ), 0 )
VAR vResult =
    SWITCH (
        TRUE (),
        vThisTime >= TIME ( 7, 0, 0 )
            && vThisTime < TIME ( 10, 20, 0 ), "1a",
        vThisTime >= TIME ( 10, 20, 0 )
            && vThisTime < TIME ( 13, 30, 0 ), "2a",
        vThisTime >= TIME ( 13, 30, 0 )
            && vThisTime < TIME ( 16, 50, 0 ), "3a",
        vThisTime >= TIME ( 16, 50, 0 )
            && vThisTime < TIME ( 19, 00, 0 ), "4a",
        vThisTime >= TIME ( 19, 0, 0 )
            && vThisTime < TIME ( 22, 20, 0 ), "1b",
        vThisTime >= TIME ( 22, 20, 0 )
            && vThisTime < TIME ( 1, 30, 0 ), "2b",
        vThisTime >= TIME ( 1, 30, 0 )
            && vThisTime <= TIME ( 4, 50, 0 ), "3b",
        "4b"
    )
RETURN
    vResult

Pat

[Anonymous]

Thank you! Here's what I ended up doing - 

First, I created a measure to pull the time out of the date/time column. 

Time Open EST = MINX('DwellBay collection', 'DwellBay collection'[DELETE Time Open EST])

Then used 

Period =

SWITCH(

TRUE(),

    TIMEVALUE([Time Open EST]) >= TIME(7,00,0) && TIMEVALUE([Time Open EST]) < TIME(10,20,0),"1a",

    TIMEVALUE([Time Open EST]) >= TIME(10,20,0) && TIMEVALUE([Time Open EST]) < TIME(14,00,0),"2a",

    TIMEVALUE([Time Open EST]) >= TIME(14,00,0) && TIMEVALUE([Time Open EST]) < TIME(16,50,0),"3a",

    TIMEVALUE([Time Open EST]) >= TIME(16,50,0) && TIMEVALUE([Time Open EST]) < TIME(19,00,0),"4a",

    TIMEVALUE([Time Open EST]) >= TIME(19,00,0) && TIMEVALUE([Time Open EST]) < TIME(22,20,0),"1b",

    TIMEVALUE([Time Open EST]) >= TIME(22,20,0) && TIMEVALUE([Time Open EST]) < TIME(23,59,59),"2b",

    TIMEVALUE([Time Open EST]) >= TIME(0,00,0) && TIMEVALUE([Time Open EST]) < TIME(2,00,0),"2b",

    TIMEVALUE([Time Open EST]) >= TIME(2,00,0) && TIMEVALUE([Time Open EST]) < TIME(4,50,0),"3b",

    TIMEVALUE([Time Open EST]) >= TIME(4,50,0) && TIMEVALUE([Time Open EST]) < TIME(7,00,0),"4b"

)

Probably overly complicated but looks like it's working appropriately. I am having some issues when adding other values to the matrix but I believe that's a table relationship issue.