Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver I

## Number of staff working each hour

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

1 ACCEPTED SOLUTION
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
9 REPLIES 9
Resolver I

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

Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Frequent Visitor

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

Helper IV

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

Super User

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" )
)
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Helper IV

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

Helper IV

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?

Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Helper IV

thank you 🙂

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors