Measure to calculate working hours based on shift patterns

[mrihussain77]

Hi,

Hi,

I have a pre-existing model with employee data, job data, shifts, shift patterns, annual leave etc etc.

What i am trying to achieve is a measure to calculate the number of working hours all employed staff would work on any date.
The issue is many people change shift patterns through their employment life time, and some people are on standard shift, mon to fri 9 to 5 every week, but others work a standard shift pattern, ie 10-2 every mon, 11-4 every tue etc, and just to complicate matters some are on a rotating shift patter, i.e. work every other week, and some only first week (every 4 weeks)
I will self-confess now, I am relatively new to power bi and do not come from a programming / SQL background.

I couldnt find a way of sharing the PBIX file directly,
link to PBIX file  in google drive
Link to Shared folder in google drive

Essentially I am expecting the output to look like screenshot below

 In this screenshot Mike and Bill are not showing any hours becuase they have not started with the company.

In this screenshot you can see Mike changed shift end on Oct 2021 so in Nov the new hours are reflected.

I do not need to report on each individual as there are 1000's of etsff, and 1000's of shift patterns. 
I am  looking to create a measure that sums the working hours but only for thos working and based on shift at the time.

I have had a look through the forum, and couldn't find anything that address this query.

Any help is much appreciate and many thanks in advance.

[mrihussain77]

Thank you for the responce, sorry but i do not understand what you mean.

If you meant a shift calendar listing each employee and their start/end times, day by day. This is not an option due to number of employees and number of shifts (each in their thousands).

If you meant one table listing each employee and start time and end time by when they started and ended that particular shift pattern. This we already have.

I am having trouble working out who should have worked how many hours on any given day. Think I may have to limit myself to monthly rather than daily or weekly.

[lbendlin · Accepted solution]

for such a chaotic setup you should ditch the shift pattern approach and go with an actual shift calendar. For each employee list all shift start and shift end times.