Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors