Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to count night shift based on time?

Hi Bros and Pros, 

I need a query or measure to identify night shift based on time working from 11:00PM-5:00PM, any time falls in this period would be consider as a night shift. 

Here is my dax:

ShiftType =
SWITCH(
    TRUE,
    'Kronos_Night'[STARTDTM.2]='Kronos_Night'[ENDDTM.2], "",
    'Kronos_Night'[STARTDTM.1].[Day] < 'Kronos_Night'[ENDDTM.1].[Day], "Night",
    AND('Kronos_Night'[StartTimeNum]>0.958233, 'Kronos_Night'[EndTimeNum] < 0.9994), "Night",
    AND('Kronos_Night'[StartTimeNum]>=0, 'Kronos_Night'[EndTimeNum] < 0.20823333), "Night",
    AND('Kronos_Night'[StartTimeNum]>=0.25, 'Kronos_Night'[EndTimeNum] < 0.29171), "Day",
    AND('Kronos_Night'[StartTimeNum]>=0.2083, 'Kronos_Night'[EndTimeNum] < 0.29171), "Day",
    AND('Kronos_Night'[StartTimeNum]>0.0005, 'Kronos_Night'[EndTimeNum] < 0.29171), "Night",
    'Kronos_Night'[StartTimeNum]=0, "Night",
    "Day")

The problem is that if one employee is working from 11:00pm - 11:15pm, 11:15pm-11:45pm, 11:45pm-12:00amnext day, 12:00am - 5:00am, all of these four shifts would be identified as night shift, but what I need is to identify these four rows as one night shift.

I am stuck by it, any help would be great;y appreciatied. 

 

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    //but what I need is to identify these four rows as one night shift.

     

    A little confused. Is this what you want the final output to look like? If so, you need to have an [Index] column that outputs the result in the row with the lowest value in the [Index] column.

     

    You can also create a new calculation table using the SUMMARIZE function, like this.

    Table =
SUMMARIZE (
    'Kronos_Night',
    'Kronos_Night'[StarDateTrue],
    'Kronos_Night'[ShiftType]
)

    If I have misunderstood your needs, please feel free to contact me.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data