Forum Discussion
Calculate team capacity based on projects and due dates
Hi everyone,
I'm trying to figure out our team's monthly capacity remaining after taking into account current assigned projects based on due dates. Because I don't know when the project’s hours will be used (just know it has to be before end date) my idea is to take the remaining hours and spread them evenly over the remaining months (remaining months includes current month and end date month). This will allow me to see how much capacity we have remaining for new projects.
My original data looks like this:
| project | assigned to | start date | end date | project hours | hours used |
| project a | person b | Jan-23 | Dec-23 | 30 | 15 |
| project b | person a | Apr-23 | Mar-24 | 20 | 5 |
| project c | person c | Sep-23 | Aug-24 | 50 | 40 |
| project d | person a | Jan-23 | Dec-23 | 100 | 75 |
| project e | person b | Feb-23 | Jan-24 | 20 | 4 |
I'm trying to figure out how to end up with something like this:
| project | assigned to | month | hours |
| project a | person b | Dec-23 | 15 |
| project b | person a | Dec-23 | 3.75 |
| project b | person a | Jan-24 | 3.75 |
| project b | person a | Feb-24 | 3.75 |
| project b | person a | Mar-24 | 3.75 |
| project c | person c | Dec-23 | 1.11 |
| project c | person c | Jan-24 | 1.11 |
| project c | person c | Feb-24 | 1.11 |
| project c | person c | Mar-24 | 1.11 |
| project c | person c | Apr-24 | 1.11 |
| project c | person c | May-24 | 1.11 |
| project c | person c | Jun-24 | 1.11 |
| project c | person c | Jul-24 | 1.11 |
| project c | person c | Aug-24 | 1.11 |
| project d | person a | Dec-23 | 25 |
| project e | person b | Dec-23 | 8 |
| project e | person b | Jan-24 | 8 |
So that I can visually see it like this:
I’ve been able to calculate number of months remaining until end date, and also number of hours per month (number of months / remaining hours). But I can’t figure out how to allocate it to those months and then graph it totaled by month. I’d also love to be able to make this a stacked bar chart with each person so I can see how much of the monthly time is attributed to each person. Any help provided will be greatly appreciated, I’m at a loss after a couple of weeks of trying to figure it out.
Hi,
This M code will generate your desired table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKz0pNLlFIVNJRKkgtKs7PU0gCMr0S83SNjIEMl9RkCMPYAEgYmirF6iA0JSE0gfQ7FhRB1PomAhkmQIYRSBOqnmSEHhAzOLUAosexNB2ixxSkx8QARVMKqkWYrjM0AOkyR7UqFdVPbqlJEMVg7XDnmSjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, #"assigned to" = _t, #"start date" = _t, #"end date" = _t, #"project hours" = _t, #"hours used" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"assigned to", type text}, {"start date", type date}, {"end date", type date}, {"project hours", Int64.Type}, {"hours used", Int64.Type}}), #"Remaining hours" = Table.AddColumn(#"Changed Type", "Remaining hours", each [project hours] - [hours used], Int64.Type), #"Month Span" = Table.AddColumn(#"Remaining hours", "Month Span", each (12 * (Date.Year([end date])-Date.Year(DateTime.Date(DateTime.LocalNow())))) + (Date.Month([end date])-Date.Month(DateTime.Date(DateTime.LocalNow()))) + (if Date.Day(DateTime.Date(DateTime.LocalNow())) > Date.Day([end date]) then -1 else 0 ) + 2), #"Monthly hours" = Table.AddColumn(#"Month Span", "Monthly hours", each [Remaining hours] / [Month Span], type number), #"Added Custom" = Table.AddColumn(#"Monthly hours", "Month List", each List.Numbers( 1, [Month Span] )), #"Expanded Month List" = Table.ExpandListColumn(#"Added Custom", "Month List"), #"Generate date" = Table.AddColumn(#"Expanded Month List", "Date", each Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),[Month List]-1)), type date), #"Removed Columns" = Table.RemoveColumns(#"Generate date",{"start date", "end date", "project hours", "hours used", "Remaining hours", "Month Span", "Month List"}) in #"Removed Columns"Hope this helps.
2 Replies
- Ashish_Mathur
Super User
Hi,
This M code will generate your desired table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKz0pNLlFIVNJRKkgtKs7PU0gCMr0S83SNjIEMl9RkCMPYAEgYmirF6iA0JSE0gfQ7FhRB1PomAhkmQIYRSBOqnmSEHhAzOLUAosexNB2ixxSkx8QARVMKqkWYrjM0AOkyR7UqFdVPbqlJEMVg7XDnmSjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, #"assigned to" = _t, #"start date" = _t, #"end date" = _t, #"project hours" = _t, #"hours used" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"assigned to", type text}, {"start date", type date}, {"end date", type date}, {"project hours", Int64.Type}, {"hours used", Int64.Type}}), #"Remaining hours" = Table.AddColumn(#"Changed Type", "Remaining hours", each [project hours] - [hours used], Int64.Type), #"Month Span" = Table.AddColumn(#"Remaining hours", "Month Span", each (12 * (Date.Year([end date])-Date.Year(DateTime.Date(DateTime.LocalNow())))) + (Date.Month([end date])-Date.Month(DateTime.Date(DateTime.LocalNow()))) + (if Date.Day(DateTime.Date(DateTime.LocalNow())) > Date.Day([end date]) then -1 else 0 ) + 2), #"Monthly hours" = Table.AddColumn(#"Month Span", "Monthly hours", each [Remaining hours] / [Month Span], type number), #"Added Custom" = Table.AddColumn(#"Monthly hours", "Month List", each List.Numbers( 1, [Month Span] )), #"Expanded Month List" = Table.ExpandListColumn(#"Added Custom", "Month List"), #"Generate date" = Table.AddColumn(#"Expanded Month List", "Date", each Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),[Month List]-1)), type date), #"Removed Columns" = Table.RemoveColumns(#"Generate date",{"start date", "end date", "project hours", "hours used", "Remaining hours", "Month Span", "Month List"}) in #"Removed Columns"Hope this helps.
- amitchandak
PBInewbie11 , To me seem like very similar HR Problem of Active employee to between date solution
refer
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
Between Dates- Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMrefer the files attached
