Staff Capacity Total Hours Available Model

Hi Everyone,

I am very new to Power Bi. I have spent a lot of time searching and playing around with various options but I am stumped as to how best the following problem should be approached.

I am wanting to build a measure that will allow me to calculate each staff members total hour capacity based on a weekly table. Due to the software I have to pull data from I don’t have a lot of options to change the base tables. For example a lot of staff will work 7.5 hours a day so 37.5 hours week, but others might work 7.5 on a Monday but 5 on a Tuesday, nothing on Wednesday and then 6 on Thursday and Friday.

So I want to be able to show total available hours for each different staff member over a day, week, month or year using the existing table

I have attached a simple version of the model I have started so far, but I am unsure whether this can be solved by creating the right relationships, the right measures or a mixture of both. I have also attached an example of the staff capacity table that gives me the hours per day. I used unpivot to load this into my data model.Staff.xlsx (17.8 KB)

I played with some variations on the working model, but I just can get it to be be able to show me a table that allows me to filter by multiple staff over varying periods and calculate the total hours they have available.
Staff Capacity.pbix (157.7 KB)

Hi @Simon1,

Welcome to the forum. I believe if you bring date in your weekly capacity table and join it with date table, you can achieve your desired results.

How to bring date? You can use left join in PQ and bring only date column in your weekly table.

Sample Model: I just did merge queries in your weekly table and created relationship. Please find attached picture and sample file.

Staff Capacity_date from days.pbix (186.2 KB)

2 Likes

Thanks Hafizsultan,

I thought it would be relatively simple, just couldn’t get my head around it.

1 Like