Number of staff working each hour

[CarlBlunck]

Hi all,

Trying to work out how I can calculate how many staff are working each hour without having to expand my fact table into, for example, 15 min intervals between each shift.  My timesheet data is pretty typical

PERSONNUM	ShiftStartTimeStamp	ShiftEndTimeStamp	PayCode
123456	01/01/2023 07:00:00	01/01/2023 15:30:00	01
987654	01/01/2023 07:00:00	01/01/2023 15:30:00	01
654987	01/01/2023 12:00:00	01/01/2023 20:30:00	01

What I want to achieve is a visual that looks like this:

I've tried:

1. Creating shift start and shift end columns that just have the time portion
2. Creating a dim table that just has the hours of the day
3. Linking the shift start and shift end columns to the dim table and making the relationships inactive
4. Plotting the hour column from the dim table along the x-axis of a column chart
5. writing this formula for the headcount

TimeSheetHeadcount =

CALCULATE(DISTINCTCOUNT(fact_TimeCardData[PERSONNUM]),

USERELATIONSHIP(fact_TimeCardData[STARTTIME],'dim_TimeSheet-Time'[Time]),

USERELATIONSHIP(fact_TimeCardData[ENDTIME],'dim_TimeSheet-Time'[Time])

)

But I am getting this error.

Any idea on the error or how else achieve the result?

Thanks
Carl

[CarlBlunck]

@danextian this is amazing, thank you so much!!!  I went with your second approach.  Works perfectly.

[danextian · Accepted solution]

Hi @CarlBlunck ,

There are two approaches in the attached pbix. The first one is by using PQ to create rows of hours included in the end and start times. The second approach require san extra table (calculated or through PQ) containing the hours in a day.

[Anonymous]

Hi @danextian,

Thank you for this solution. I have questions for each alternative.

1. The shifts passed midnight dont expand as rows with let in formula, how can i fix it?

2. Person per hour calculation gives me the same (aggregated) result for everyday, how can I get a result split into days? 

+ 
3. I need to calculate Sales per head count, but dividing sales into Person per Hour doenst work. What should I do? 

Thanks,

Serhan

[Reine]

Hi @danextian , 

I am so glad I found this!  I needed to show person per hour and your first approach worked perfectly for me 🙂 
I also need to show person by half hour.  How can I use the first approach you attached to do this?

Thank you for your help,

Reine

[danextian]

Hi @Reine ,

Try changing the Hours table to this. Notice that I added the 0.5 increment in the GENERATESERIES function and changed the end value to 23.5 to account for the half an hour increment.

Hours = 
ADDCOLUMNS (
    GENERATESERIES ( 0, 23.5, 0.5 ),
    "hour of the day",
        VAR __val = [Value]
        VAR _time =
            IF ( __val > 12, __val - 12, __val )
        VAR _ampm =
            IF ( __val >= 12, "pm", "am" )
        RETURN
            IF (
                [Value] = 12,
                "12:00 pm",
                IF (
                    [Value] = 0,
                    "12:00 am",
                    FORMAT ( DIVIDE ( _time, 24 ), "hh:mm" ) & " " & _ampm
                )
            ),
    "hour of the day2", FORMAT ( DIVIDE ( [Value], 24 ), "hh:mm" )
)

[Reine]

Hi @danextian , Sorry to bother you again. I went ahead and tried to do it with this approach and it does not work.  I notice that in your original file, it doesn't seem to be working properly.  In your model, there is a person working until 8:30 pm, but that person isn't being counted at that time.  I don't know enough about the code you are using to figure out where the issue is 😞  If you have time can you take a look?  

Thank you,

Reine

[Reine]

Thank you @danextian  - I actually applied the other approach you demonstrated to my data instead of this one that uses this extra Hours table, because I couldn't quite get this approach to work with my data.  If you have time and are so inclined, can you tell me how to do this with the other approach?

[danextian]

Oops. I forgot to change the increment for end time. Should have been 0.5/24  instead of 1/24 to account for the half hour interval.  Please see the attached pbix for the details.

[Reine]

thank you 🙂