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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CarlBlunck
Resolver I
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

 

PERSONNUMShiftStartTimeStampShiftEndTimeStampPayCode
12345601/01/2023 07:00:0001/01/2023 15:30:0001
98765401/01/2023 07:00:0001/01/2023 15:30:0001
65498701/01/2023 12:00:0001/01/2023 20:30:0001

 

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

CarlBlunck_0-1676354369134.png

 

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.
 
CarlBlunck_1-1676355165530.png

 

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

 

Thanks
Carl

1 ACCEPTED SOLUTION
danextian
Super User
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.

danextian_0-1676380952058.png

 










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.

View solution in original post

9 REPLIES 9
CarlBlunck
Resolver I
Resolver I

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

danextian
Super User
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.

danextian_0-1676380952058.png

 










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.
Anonymous
Not applicable

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

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

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.

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

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?

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.

thank you 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors