Power BI Exchange

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
------------------------------

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
------------------------------

Original Message:
Sent: Jul 18, 2022 05:30 PM
From: Jade C
Subject: Number of staff on shift per hour

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
------------------------------