Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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:
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:
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:
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":
// 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
Thank you for spotting a mystake I should have seen.
I'm nearly there (should finalize tomorow).
Big thanks
Fab
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |