I want to define start time and end time for the day.

[ShreyasK]

I am preparing dashboard for manufacturing unit which runs day & night shifts. 1st Shift of the day starts at 05:30 AM and last shift ends on next day 05:29 AM. 

Since there are many IN & Out entries post 0:00 midnight, Date is getting changed to next day but actual employees are present for previous day shift.

As show in the below table, Employees whose InTime is greater than 0:00 midnight for them date is showing as 01/19/2023 however those employees are present for 01/18/2023 night shift.

Network ID	Username	Date	InLoc	InTime	OutLoc	OutTime	Duration
ABC	Nikita	01/18/2023	Flap Barrier	22:23	FF ENTRY	5:24	7:22
EFG	Shreyas	01/18/2023	SF MFG ENTRY	23:23	Flap Barrier Exit 2	5:25	6:02
HIG	Pallavi	01/19/2023	FF ENTRY	0:19	Bike Barrier Exit	7:03	6:44
KLM	Sanket	01/19/2023	FF ENTRY	0:20	Bike Barrier Exit	6:55	6:35
NOP	Vijay	01/19/2023	FF ENTRY	0:28	SF MFG ENTRY	5:45	5:17

Can someone please guide me...? How those employees presence can be shown for 01/18/2023 day when we filter the days in dashboard.

[ChrisMendoza · Accepted solution]

@ShreyasK - I think the easiest way might be to add a column [ShiftDate] either in Power Query or Dax with this type of logic:

[ShreyasK]

Thanks a lot Chris for help, This worked but I just noticed small problem in my table. There are some employees whose InTime is "null" because of which I am getting an error in ShiftDate column.

In such scenario, If InTime is "Null" and OutTime < 05:29 AM then I would like to see 01/18/2023   instead of 01/19/2023.

Can you please guide how this can be archieved..?

[FreemanZ]

hi @ShreyasK 

with DAX code, the code for a calculated column would look like:

Date2 =
IF(
    [InTime]=BLANK() || [OutTime]<=TIME(5,29,0),
    [Date]-1,
    [Date]
)