Monthly workload summing each phase from each project

[Fab117]

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:

Projet	Owner	Activity	Phase	Workload [%]	Start date	Due date
12154256	Paul	Activity 01	Initiation	5%	14.10.2023	25.11.2023
12154256	Paul	Activity 02	Planning	25%	25.11.2023	18.12.2023
12154256	Paul	Activity 03	Planning	25%	25.11.2023	07.01.2024
12154256	Paul	Activity 04	Execution & Control	15%	07.01.2024	09.02.2024
12154256	Paul	Activity 05	Execution & Control	15%	25.01.2024	17.03.2024
12154256	Paul	Activity 06	Execution & Control	15%	08.05.2024	09.06.2024
12154256	Paul	Activity 07	Execution & Control	15%	12.02.2024	10.10.2024
12154256	Paul	Activity 08	Execution & Control	15%	19.03.2024	22.04.2024
12154256	Paul	Activity 09	Closure	10%	09.06.2024	25.06.2024
546897426	Michel	Activity 01	Initiation	5%	03.08.2023	11.10.2023
546897426	Michel	Activity 02	Initiation	5%	09.09.2023	28.09.2023
546897426	Michel	Activity 03	Planning	25%	11.10.2023	13.12.2023
546897426	Michel	Activity 04	Planning	25%	25.10.2023	17.12.2023
546897426	Michel	Activity 05	Planning	25%	12.11.2023	28.11.2023
546897426	Michel	Activity 06	Execution & Control	15%	17.12.2023	15.01.2024
546897426	Michel	Activity 07	Execution & Control	15%	24.02.2024	25.04.2024
546897426	Michel	Activity 08	Execution & Control	15%	04.01.2024	17.03.2024
546897426	Michel	Activity 09	Execution & Control	15%	07.02.2024	25.04.2024
546897426	Michel	Activity 10	Closure	10%	17.05.2024	08.06.2024
546897426	Michel	Activity 11	Closure	10%	25.04.2024	12.05.2024
1654646168	Paul	Activity 01	Initiation	5%	15.12.2023	22.01.2024
1654646168	Paul	Activity 02	Planning	25%	22.01.2024	15.03.2024
1654646168	Paul	Activity 03	Execution & Control	15%	15.03.2024	19.06.2024
1654646168	Paul	Activity 04	Closure	10%	19.06.2024	18.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:

Phase	Workload
Initiation	5%
Planning	25%
Execution & Control	15%
Closure	10%

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

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):

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:

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

[vojtechsima · Accepted solution]

Hello, @Fab117, 
I think I got you:

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

[Fab117]

@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

[vojtechsima]

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.

[Fab117]

Hi @vojtechsima,

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

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

[vojtechsima]

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?

[Fab117]

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":

    // 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:

Any idea on what is wrong?

Fab

[vojtechsima]

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

[Fab117]

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

Fab