Calculate team capacity based on projects and due dates

[PBInewbie11]

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.

[Ashish_Mathur · Accepted solution]

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-tr...
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/O653vwLTUzM

refer the files attached