cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
artfulmunkeey
Helper I
Helper I

Issue matching (dates) MS Project Capacity & demand to calculate utilisation

Hi all, I am relatively new to Power Queries and have been learning through trial and error.

 

I have one particular issue which I have been struggling with for some time and various attempts to solve it leave me even more confused.

 

I have two datasets (ODATA) from MS Project - ResourceDemandTimePhased (Demand by Resource and Project - per month) & ResourceTimePhased (Capacity by Resource - per day).

 

They resemble the below tables.

ResourceDemandTimePhased
Proejct IDResource IDDateDemand
Project 1Resource 1Jan-20100
Project 1Resource 1Feb-2080
Project 1Resource 1Mar-20120
Project 1Resource 2Feb-20100
Project 1Resource 2Mar-2080
Project 1Resource 3Jan-20100
Project 2Resource 1Feb-2020
Project 2Resource 1Mar-2030
Project 2Resource 2Mar-2020
Project 2Resource 2Apr-2050
Proejct 2Resource 3Feb-20100

 

ResourceTimePhased
Resource IDDate Capacity
Resource 101/01/20208
Resource 102/01/20208
Resource 103/01/20208
Resource 104/01/20208
Resource 201/01/20208
Resource 202/01/20208
Resource 203/01/20208
Resource 204/01/20208
Resource 301/01/20208
Resource 302/01/20208
Resource 303/01/20208
Resource 304/01/20208

 

I want to calcualte a percentage utilisation (Demand/Capacity) for each Resource per month and be able to filter by project, however, I cannot match the data.

 

I have attempted to merge the two, datasets, but cannot do it correctly and end up with the wrong values as I either end up with too many days, or duplicate entries for projects. 

 

I believe I need to somehow sum the capacity monthly whilst grouping by Resource, before merging the two sets to add a measure to calculate the percentage. But struggling to do this and understand how to retain the Project filter.

 

I cannot get my head around it.

 

Can anyone please advise?

 

Thanks in advance!

1 ACCEPTED SOLUTION
Jimmy801
Super User III
Super User III

Hello @artfulmunkeey 

 

here another approach.

Check it out

let
    Demand = 
    let
	Source = #table
	(
		{"Proejct ID","Resource ID","Date","Demand"},
		{
			{"Project 1","Resource 1","Jan-20","100"},	{"Project 1","Resource 1","Feb-20","80"},	{"Project 1","Resource 1","Mar-20","120"},	{"Project 1","Resource 2","Feb-20","100"},	
			{"Project 1","Resource 2","Mar-20","80"},	{"Project 1","Resource 3","Jan-20","100"},	{"Project 2","Resource 1","Feb-20","20"},	{"Project 2","Resource 1","Mar-20","30"},	
			{"Project 2","Resource 2","Mar-20","20"},	{"Project 2","Resource 2","Apr-20","50"},	{"Proejct 2","Resource 3","Feb-20","100"}
		}
	),
    changetype = Table.TransformColumnTypes(Source, {{"Demand", Int64.Type}}),
	AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) ))
    in
	    AddMonthYear,

	Capacity = 
	let
	Source = #table
	(
		{"Resource ID","Date","Capacity"},
		{
			{"Resource 1","01/01/2020","8"},	{"Resource 1","02/01/2020","8"},	{"Resource 1","03/01/2020","8"},	{"Resource 1","04/01/2020","8"},	{"Resource 2","01/01/2020","8"},	
			{"Resource 2","02/01/2020","8"},	{"Resource 2","03/01/2020","8"},	{"Resource 2","04/01/2020","8"},	{"Resource 3","01/01/2020","8"},	{"Resource 3","02/01/2020","8"},	
			{"Resource 3","03/01/2020","8"},	{"Resource 3","04/01/2020","8"}
		}
	),
	changetype = Table.TransformColumnTypes(Source, {{"Capacity", Int64.Type}}),
	AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) ))
	in
		AddMonthYear,
	Join = Table.NestedJoin(Demand, {"Resource ID", "MonthYear"}, Capacity, {"Resource ID", "MonthYear"}, "Capacity"),
    #"Aggregated Capacity" = Table.AggregateTableColumn(Join, "Capacity", {{"Capacity", List.Sum, "Sum of Capacity"}})
in
    #"Aggregated Capacity"

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

2 REPLIES 2
Jimmy801
Super User III
Super User III

Hello @artfulmunkeey 

 

here another approach.

Check it out

let
    Demand = 
    let
	Source = #table
	(
		{"Proejct ID","Resource ID","Date","Demand"},
		{
			{"Project 1","Resource 1","Jan-20","100"},	{"Project 1","Resource 1","Feb-20","80"},	{"Project 1","Resource 1","Mar-20","120"},	{"Project 1","Resource 2","Feb-20","100"},	
			{"Project 1","Resource 2","Mar-20","80"},	{"Project 1","Resource 3","Jan-20","100"},	{"Project 2","Resource 1","Feb-20","20"},	{"Project 2","Resource 1","Mar-20","30"},	
			{"Project 2","Resource 2","Mar-20","20"},	{"Project 2","Resource 2","Apr-20","50"},	{"Proejct 2","Resource 3","Feb-20","100"}
		}
	),
    changetype = Table.TransformColumnTypes(Source, {{"Demand", Int64.Type}}),
	AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) ))
    in
	    AddMonthYear,

	Capacity = 
	let
	Source = #table
	(
		{"Resource ID","Date","Capacity"},
		{
			{"Resource 1","01/01/2020","8"},	{"Resource 1","02/01/2020","8"},	{"Resource 1","03/01/2020","8"},	{"Resource 1","04/01/2020","8"},	{"Resource 2","01/01/2020","8"},	
			{"Resource 2","02/01/2020","8"},	{"Resource 2","03/01/2020","8"},	{"Resource 2","04/01/2020","8"},	{"Resource 3","01/01/2020","8"},	{"Resource 3","02/01/2020","8"},	
			{"Resource 3","03/01/2020","8"},	{"Resource 3","04/01/2020","8"}
		}
	),
	changetype = Table.TransformColumnTypes(Source, {{"Capacity", Int64.Type}}),
	AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) ))
	in
		AddMonthYear,
	Join = Table.NestedJoin(Demand, {"Resource ID", "MonthYear"}, Capacity, {"Resource ID", "MonthYear"}, "Capacity"),
    #"Aggregated Capacity" = Table.AggregateTableColumn(Join, "Capacity", {{"Capacity", List.Sum, "Sum of Capacity"}})
in
    #"Aggregated Capacity"

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

BA_Pete
Super User II
Super User II

Hi @artfulmunkeey ,

 

Try this and see if it does what you want:

Go to New Source>Blank Query, then in Advanced Editor paste my code over the default code for each table below. Take note of the names you need to call them commented in the first row of each:

 

 

//Call this query ResourceTimePhased
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk5VMFTSUTIw1AciIwMjAyDHQilWB13eiIC8MQF5E9zyRgTsNyJgvxEB+40I2G9MwH5jAvYbE7DfGIv9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Resource ID" = _t, Date = _t, Capacity = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"Resource ID", type text}, {"Date", type date}, {"Capacity", Int64.Type}}),
    addStartOfMonth = Table.AddColumn(chgAllTypes, "StartOfMonth", each Date.StartOfMonth([Date]), type date),
    #"groupResource,SoM" = Table.Group(addStartOfMonth, {"Resource ID", "StartOfMonth"}, {{"Capacity", each List.Sum([Capacity]), type number}})
in
    #"groupResource,SoM"

 

 

 

//Call this query ResourceDemandTimePhased
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRCkotzi8tSk4Fc7wS83SNDIAMQwMDpVgdPCrdUpMgKi0IKPRNLIIaaYRHpRGykXgtN0I2E5/lxvj8Y4TTP0YEFMLtNsajEMWR+EwEcRwLoApN4QpTs9AVGmMEUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [projectID = _t, resourceID = _t, Date = _t, Demand = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"projectID", type text}, {"resourceID", type text}, {"Date", type date}, {"Demand", Int64.Type}}),
    mergeResourceTimePhased = Table.NestedJoin(chgAllTypes, {"resourceID", "Date"}, ResourceTimePhased, {"Resource ID", "StartOfMonth"}, "ResourceTimePhased", JoinKind.LeftOuter),
    expandResourceTimePhased = Table.ExpandTableColumn(mergeResourceTimePhased, "ResourceTimePhased", {"Capacity"}, {"Capacity"})
in
    expandResourceTimePhased

 

 

 

You should now be able to follow the steps I took to merge these together. Summary:

1) Added [StartOfMonth] column to ResourceTimePhased, then grouped on this and [Resource ID] with sum of Capacity.

2) Merged ResourceDemandTimePhased [Resource ID] & [Date] with ResourceTimePhased [Resource ID] & [StartOfMonth]

 

This gives me the following output:

artfulmunkeey.PNG

 

*NB* the ResourceTimePhased query has no awareness of Project, so capacity is just applied in its total to each [Resource ID] in the merged query, regardless of the project.

 

Pete

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors