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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Fab117
Helper IV
Helper IV

Monthly workload summing each phase from each project

Hello,

 

I'm looking for the best approach to represent monthly workload for all our projects considering theoretical workload (field [Workload [%]]) for each phases.

 

I've a data set looking this:

ProjetOwnerActivityPhaseWorkload [%]Start dateDue date
12154256PaulActivity 01Initiation5%14.10.202325.11.2023
12154256PaulActivity 02Planning25%25.11.202318.12.2023
12154256PaulActivity 03Planning25%25.11.202307.01.2024
12154256PaulActivity 04Execution & Control15%07.01.202409.02.2024
12154256PaulActivity 05Execution & Control15%25.01.202417.03.2024
12154256PaulActivity 06Execution & Control15%08.05.202409.06.2024
12154256PaulActivity 07Execution & Control15%12.02.202410.10.2024
12154256PaulActivity 08Execution & Control15%19.03.202422.04.2024
12154256PaulActivity 09Closure10%09.06.202425.06.2024
546897426MichelActivity 01Initiation5%03.08.202311.10.2023
546897426MichelActivity 02Initiation5%09.09.202328.09.2023
546897426MichelActivity 03Planning25%11.10.202313.12.2023
546897426MichelActivity 04Planning25%25.10.202317.12.2023
546897426MichelActivity 05Planning25%12.11.202328.11.2023
546897426MichelActivity 06Execution & Control15%17.12.202315.01.2024
546897426MichelActivity 07Execution & Control15%24.02.202425.04.2024
546897426MichelActivity 08Execution & Control15%04.01.202417.03.2024
546897426MichelActivity 09Execution & Control15%07.02.202425.04.2024
546897426MichelActivity 10Closure10%17.05.202408.06.2024
546897426MichelActivity 11Closure10%25.04.202412.05.2024
1654646168PaulActivity 01Initiation5%15.12.202322.01.2024
1654646168PaulActivity 02Planning25%22.01.202415.03.2024
1654646168PaulActivity 03Execution & Control15%15.03.202419.06.2024
1654646168PaulActivity 04Closure10%19.06.202418.07.2024

 

What I'm looking to do is to have the full workload month by month.

 

NB: At this stage Owner and Activity are just for information now (later, I'll had the possibility to filter by Owner)

 

NB2: Workload is always the same by project phase:

PhaseWorkload
Initiation5%
Planning25%
Execution & Control15%
Closure10%

 

=> for each single project, earliest start date and latest due date by phase should be considered

Support picture 1.jpg

 

The 1st step is probably for each line of my table to identify the earliest start date/latest due date by phase for each project (I've been able to do it):

Support picture 3.jpg

 

Next step would probably be to build a virtual (or real) table from earliest start date (here: August 23) to latest due date (here: October 24) and calculate each month the workload by project and phase according below illustration:

Support picture 2.jpg

 

Would someone know how I can get there (or simpler approach to get the "Total worload by month" in my upper example)?

 

Thanks in advance.

 

Have a great week-end.

 

Fab

1 ACCEPTED SOLUTION
vojtechsima
Resident Rockstar
Resident Rockstar

Hello, @Fab117
I think I got you:

vojtechsima_0-1697297088424.png

Btw I think I found some issues in your calculations as 31-3 = 28 but it's not 93% but 90% but other than that it seems working. Based on your example.

 

Here's the file:

Monthly workload summing each phase from each project.pbix

View solution in original post

8 REPLIES 8
vojtechsima
Resident Rockstar
Resident Rockstar

Hello, @Fab117
I think I got you:

vojtechsima_0-1697297088424.png

Btw I think I found some issues in your calculations as 31-3 = 28 but it's not 93% but 90% but other than that it seems working. Based on your example.

 

Here's the file:

Monthly workload summing each phase from each project.pbix

@vojtechsima 

Big thank you for spending time on my challenge.

Your proposal is great. I was not expecting to look for the solution in Power Query, but why not.

After deep review (I'm quite new in Power BI), I think I understood all your steps up to the result.

I'll now try to apply to my specific case.

 

Have a nice week.

 

Fab

Well, it's easier to have a physical table for this kind of stuff because doing it in DAX would be just a pain 😄 

Happy to help, kudos are appreciated. Lemme know if you need some steps better explained.

Hi @vojtechsima,

I've been able to manage up to creation of the "key" column:

Fab117_0-1697556736931.png

I then understand that for each different [key], you'll identify oldest [start date] and latest [Due date] through steps "startDate" and "endDate" which will result to the 2 new columns [earliestStartDate] and [latestDueDate] (that you'll then expand).

 

I don't understand what is behind steps "startDate" and "endDate" up to the 2 new columns creation and how to replicate in my particular case.

 

Could you please detail this part?

 

Regards,

 

Fab

 

 

Hello, @Fab117 .

The StartDate and EndDate for sake of readability is a function.

This function have couple steps that will be proceed when this function it's called. Both of them are based on the intital table which is the Key variable (step).

 

What I do for each is to based on the [key] I sort it either by StartDate or EndDate Asc or Desc and then Buffer it to keep the sorting in memory (if you don't buffer, the engine will ignore the sort when you want to remove duplicates etc)., then I remove the duplicates based on the key, so for each project and phase I have single date.
After that I join these two new columns one by one to the original table (key variable). and then I proceed with the other steps.

 

Did it clarify the solution for you?

Thanks @vojtechsima,

I spent few more hours on it this morning.

Finaly, I tried to mimic your code in adanced editor until your step "Removed Duplicates1":

Picture.jpg

 

 

    // Ajout de la colonne "key"
   key = Table.AddColumn(#"Changed Type", "key", each Text.Combine( {Text.From([ProjectId]), [Projects.ProjectOwnerName], [Project Phase] }), type text),
    
    startDate = ()=>
    let
    #"Sorted Rows" = Table.Sort(Source,{{"TaskBaselines.TaskBaselineStartDate", Order.Ascending}}),
    Buffer = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(Buffer, {"key"})
    in
    #"Removed Duplicates",
    
    endDate = ()=>
    let
    desc = Table.Sort(key,{{"TaskBaselines.TaskBaselineFinishDate", Order.Descending}}),
    Buffer = Table.Buffer(desc),
    #"Removed Duplicates" = Table.Distinct(Buffer, {"key"})
    in
    #"Removed Duplicates",
    startDateColumn = Table.AddJoinColumn(key, "key", startDate(), "key", "earliestStartDate"),
    expandColumnStart = Table.ExpandTableColumn(startDateColumn, "earliestStartDate", {"TaskBaselines.TaskBaselineStartDate"}, {"earliestStartDate"}),
    endDateColumn = Table.AddJoinColumn(expandColumnStart, "key", endDate(), "key", "latestDueDate"),
    expandColumnEnd = Table.ExpandTableColumn(endDateColumn, "latestDueDate", {"TaskBaselines.TaskBaselineFinishDate"}, {"latestDueDate"}),
    #"Removed Duplicates1" = Table.Distinct(expandColumnEnd, {"key", "earliestStartDate", "latestDueDate"})
in
    #"Removed Duplicates1"

 

But I face an error I was not able to solve:

Fab117_2-1697626300335.png

Any idea on what is wrong?

 

Fab

@Fab117  in your function, you're referring to "Source" not a "key"

vojtechsima_0-1697629280714.png

 

Thank you for spotting a mystake I should have seen.
I'm nearly there (should finalize tomorow).
Big thanks

Fab

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.