Forum Discussion
Monthly workload summing each phase from each project
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 .
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 thanksFab