Trying to calculate staff capacity to do work.
I have a Fact table ('Work Tracker') where each row = Work Item, with each item having a start and end date and up to 7 resources with the capacity required for each (held as a decimal where Full Time Equivilant = 1.0) e.g. Resource Name 1, Capacity 1, Resource Name 2, Capacity 2 etc.
We have an organisation 'dimDates' table which looks extensive enough, and I have inactive 1..* relationship from the dimDates table to the Start and End dates in the 'Work Tracker' Fact table.
I know I'll need a measure(s?), but can't work out the DAX and am stuck.
I need to be able to sum up the total capacity across all work and show a month by month view on a line and stacked column chart (my line comes from another table which is working which shows the average availability).
I also need to be able to order the months correctly, ideally the start month on the visual = first month out of data from Start Dates and the last month out of the data from End Dates, handling any blanks where no Capacity recorded for a given month, and showing the year below the months.
The Y axis 'FTE Count' is the sum of Capacity for a given month
The report will need to be able to handle month start and end, and where the Start Date and End Dates are not the beginning or end of the month.
The stack is to show by Deputy Director column for each month
An additional bonus would be to then see by individual Resource Name, although this could be on a separate report page.
'Work Tracker' table below:
|
Thanks
Hi @lbendlin
thanks for your reply.
If we took your work to date on this, and the DAX, and then divided the 'worked figure' by working days (days - weekend and public holidays) in a month, this might give us the monthly capacity used, agree?
This would need to be applied and work at severa levels, I think;
individual resource, Deputy Director and a single figure for the whole team (that view is needed as a total of capacity used across the team.)
eg
Jim worked 5 out of 23 working days in Jan: 5/23 = 0.27 capacity used
Whole team in Jan: 147 / 23 = 6.4 capacity used
How would the DAX look then?
It isn't pin point accurate- but gives an indicative figure.
Ps - what is the "sm" , [sm] in the DAX measure referring to?
Hi there
so capacity currency as stated is FTE (|Full Time Equivilant) where a Full Time employee is 1.0. We use FTE as it is both a way of determining headcount, but also in more granularity, capacity.
In this report, the data is captured under 'Resource 1 Capacity' and similar columns, and so how much capacity an item of work requires is captured by staff member(s) and expressed as the decimal figure e.g. 0.1, 0.5 etc.
The Y axis in the report expresses the total FTE, and the lines (form another table that is working fine) show the full FTE count (red line) and the maximum capacity (orange) we should not go beyond as we then impact on the time needed to do other responsibilities (line management, admin, learning etc).
The calendar table does seem to have a weekday column (values for what day of the week it is) and a 'WeekdayWeekend' column where values are one or the other depending obviously on the day of the week. There is also a 'IsBankHoliday' column for the public holidays.
The business hours aren't relevant I think, the timezone is GMT, but I also think its not relevant!
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
