Shift Report over 24hr clock by Time and Day

[Anonymous]

Good Morning,

I am trying to creatr a report from some shift data.  For example a person Starts there Shift at 08:00 in the morning and finishes at 17:00.  I want to report on the number of people that are working during any 15 minute period, given that employees will start and end at different times.

I have been able to to write some DAX code that will allocate the shift pattern across the different 15 miunte periods. However I run in to problems when trying to aggregate the results.

The digram belwo shows the 3 rows of data (3 shifts) and the results.  which it shows for example that it is aggregating to 3 for 9.45 instead of 1

The Dax code for the formula is:

Step Through Time Calculation V3 =

VAR MaxTime = MAXX(TimeTable,[Time]) -- 23:59:59

VAR MinTime = MINX(TimeTable,[Time]) -- 00:00:00

VAR ShiftStartTime = MINX(shifts,[SharedstartTime])

VAR ShiftEndTime = MAXX(Shifts,[SharedEndTime])

VAR ShiftTime = ShiftEndTime - ShiftStartTime -- 07:15:00

VAR TimeFromStartDate = MAXX(Shifts,[SharedStartTime]) - MinTime-- 08:00:00

VAR TimeFromEndate = MAXX(Shifts,[SharedEndTime]) - MaxTime -- + ShiftTime -- 15:15:00

VAR MaxDate = MAXX('Calendar',[Date])

VAR InShift = IF(AND(MaxTime>=ShiftStartTime, MinTime <= ShiftEndTime),[Shiftcount],0)

RETURN

if(HASONEVALUE('Shifts'[Date]),Inshift,sumx(VALUES('Shifts'[Date]),[Shiftcount]) )

any help on how to resolve wouldl be grateful

[mahoneypat · Accepted solution]

Here is how I would approach this in a matrix visual.

1.  Make a disconnected table of times in the query editor with = List.Times(#time(0,0,0),96,#duration(0,0,15,0)), convert to table, rename it, and load it.  

2. Make a matrix visual with this new column as the columns

3.  Use a measure like this to count the number of people working during that timeslot.  TimeSlot is the table with the 15 min increments from step 1)

Num of Employees =
VAR mintime =
    MIN ( Timeslots[TimeSlot] )
VAR maxtime =
    MAX ( Timeslots[Timeslot] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table[EmployeeID] ),
        Table[StartTime] <= maxtime,
        Table[StopTime] >= mintime
    )

You should be able to use your Date column on rows in the matrix.

Regards,

Pat

[Anonymous]

Thanks Thats great.  I think I over thought this solution.

Any ideas of how to get this to work when the start time is in one day but the end time is in the next day.

for example > StartDate and Time  30/10/2020  22:00     > EndDate and Time 31/10/2020  05:00

thsi is neded for peopel who work nights 

thanks 🙂

[Anonymous]

Time Table code is like this:

TimeTable =

VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])

RETURN

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable,SecondsTable),"Time", TIME([Hour], [Minute], [Second]),

"Time15", If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("00:15:00"),TIMEVALUE("00:15:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("00:30:00"),TIMEVALUE("00:30:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("00:45:00"),TIMEVALUE("00:45:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("01:00:00"),TIMEVALUE("01:00:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("01:15:00"),TIMEVALUE("01:15:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("01:30:00"),TIMEVALUE("01:30:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("01:45:00"),TIMEVALUE("01:45:00"),

If(TIME([Hour], [Minute], [Second])<=TIMEVALUE("02:00:00"),TIMEVALUE("02:00:00"),

and data model..