Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Number of staff on shift per hour

  • 1.  Number of staff on shift per hour

    Posted Jul 18, 2022 05:31 PM
    Hi there,

    I am hoping someone can assist me please. I am creating a resource sheet on Power Bi, the project is almost done however I am struggling to complete one last step. I want to count the staff on shift for each hour. The business runs over a 24/7 roster so I will need to count hours overnight into the following day. The data I am working with basically has a Name, Start time, Finish time.

    I have received half of my answer on a previous thread here >>> Hours at Work

    I am using this
    {Time.Hour([Start])..Time.Hour([End]),if [End] > [Start] then (Time.Hour([End]))-(Time.Hour([Start])) else 24 - (Time.Hour([Start])-Time.Hour([End]))}
     it is working well except 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. 

    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.
    I hope that made sense

    Jade C

  • 2.  RE: Number of staff on shift per hour

    Posted Jul 21, 2022 02:40 PM

    Hi Jade,

    With NO date, NO time or Date/Time how do you expect to do this?
    Especially for night shifts.
    Especially with 12 hrs clock.

    With some strange logic in data? Logically 'End Hour' - 'Start Hour' = 'Hours At Work'

    How 5-1 = 1 for 1st row? or 2 for 2nd?

    Don't get it.

    Adam Artur Boltryk
    Business Analyst

  • 3.  RE: Number of staff on shift per hour

    Posted Jul 22, 2022 02:20 AM
    Edited by Jade C Jul 22, 2022 02:51 AM