Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Number of employees per hour

    Posted Jul 19, 2021 01:14 PM
    Hello everyone,

    Lately I  started using Power BI for my project in order to analyse a team efficency,
     I have a table that is in fact an attendance list (or a work schedule) that contains data from the first january: name of employees, code, date, starting hour, ending hour, duration,
    I want to know how many employees work per hour based on their schedule, like at 8 am we have 2 employees working,
    I have tried a lot of functions, but still I 'am just a beginner so maybe I'am messing something.
    here is a screen of a part of my table :

    I want to get a visual like this, that represents the number of workers per hour,

    I really need some help here, this is a very important project for me , and this is my final step to get it done :)
    Thank you,



    ------------------------------
    sara nach
    methods
    ------------------------------


  • 2.  RE: Number of employees per hour

    Posted Jul 20, 2021 09:07 AM
    Hello @Sowmya Balakrishnan

    I tried some solutions from  the link you sent me but it's not working, I still can't get the number of hours,
    here is my problem in detail
    I hope you can help me ​​
    thank you

    ------------------------------
    sara nach
    methods
    ------------------------------



  • 3.  RE: Number of employees per hour

    Posted Jul 20, 2021 10:44 AM
    Hi Sara,

    Perhaps you could add a column in that only takes hours. In Power query you could just extract the hour and save it as a whole number.
    Then, for the graph you should be able to put the employee as a count and you have your date and hour as the x-axis.

    Kind regards,
    Rebekah

    ------------------------------
    Rebekah Alexander
    Data Analyst
    ------------------------------



  • 4.  RE: Number of employees per hour

    Posted Jul 21, 2021 03:09 AM
    Hi Rebekah,

    I've tried this before, but it's not working because my purpose is to get all the hours between the start-hour and end-hour,
    when I extract the hour, it gives me just the hour of start. I want to create a column of all hours so that I can count the employees
    For example:
    Employee X working from 08:00 to 16:00
    Employee Y working from 08:00 to 10:00
    I want to get a graph that shows 2 employees at 08:00 09:00 10:00 and 1 employee fo the rest
    It's the x-axis for hours that confuses me.
    (Sorry for my english it's ot that good)
    Thank you,
    Sara

    ------------------------------
    sara nach
    methods
    ------------------------------



  • 5.  RE: Number of employees per hour

    Posted Jul 21, 2021 06:52 AM
      |   view attached
    Hi Sara,

    I think I understand what you are saying now. I readjusted the calculation and used some dummy data to try it out.
    I added some custom columns in power query and a list of hours worked for each employee.
    Could this be something you could use? I have attached the pbix file so you can see the steps in detail.


    1. After having created a Start hour and an End hour for each employee
    Custom Column = {Number.From([start hour])..Number.From([End hour]) }​
    2. Expand to new rows

    ------------------------------
    Rebekah Alexander
    Data Analyst
    ------------------------------

    Attachment(s)



  • 6.  RE: Number of employees per hour

    Posted Jul 21, 2021 07:04 AM
    Hi Rebekah,

    I was working on this an hour ago before seeing your reply, and it worked perfectly.. (this is exactly what I have done) :)
    Now I'am working on creating relationships between my tables.

    Thanks a lot for your help


    ------------------------------
    sara nach
    methods
    ------------------------------



  • 7.  RE: Number of employees per hour

    Posted Feb 03, 2022 03:14 PM
    Dear Rebekah
    Your solutions works perfectly, but when i have a start date and end date from two diffrent calendar days, the additional column "HoursAtWork" returns "null".

    For example:


    How this can be solved?

    Thanks in advance.
    Maciej

    ------------------------------
    Mac Gapa

    ------------------------------
    Mac Gapa
    ------------------------------



  • 8.  RE: Number of employees per hour

    Posted Feb 15, 2022 01:31 PM
    Czesc Macieju!

    First of all - look on FORMAT of your column 'HoursAtWork'. It suggest that it was imported. If I were you I'll just delete this column from model (in Query) if data comes with it and add own, calculated column as  HoursAtWork = [EndDatetime] - [StartDateTime]

    Pozdro

    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 9.  RE: Number of employees per hour

    Posted Jul 08, 2022 07:21 PM
    Hi there, I have just found this solution which has helped me as I had the same problem as the original post. I have applied your formula
    Custom Column = {Number.From([start hour])..Number.From([End hour]) }​​

    This worked well for me, except I have overnight shifts which were resulting in 'null' fields. So I added the below, however this is creating an annoying error that I cannot wrap my head around. This counting counting each hour as its supposed to, then the last hour of each shift results as the duration of the shift. 

    {Time.Hour([Start])..Time.Hour([End]),if [End] > [Start] then (Time.Hour([End]))-(Time.Hour([Start])) else 24 - (Time.Hour([Start])-Time.Hour([End]))}

    I have attached an image below to outline the issue, the highlighted numbers are the final hours which display hours between start and finish time.

    Is any one able to help? Thank you in advance :)




    ------------------------------
    Jade C
    ------------------------------