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)
