Forum Discussion
Calculated critical path table using two columns with MAX(Finish) of a third date column
Hello all,
In scheduling terms, I am trying to build a calculated Critical Path table. I want to return the last Project to finish per Building based on the MAX finish date of the activities within each project.
| Building | Project | Activity | FinishDate |
| A | Replace Roof | Demo old | 1/1/2020 |
| A | Replace Roof | replace wood | 1/2/2020 |
| A | Replace Roof | coat | 1/3/2020 |
| A | Replace Roof | inspect roof | 1/4/2020 |
| A | Paint Walls | Scrape | 1/15/2020 |
| A | Paint Walls | Sand | 1/16/2020 |
| A | Paint Walls | Primer | 1/17/2020 |
| A | Paint Walls | Paint | 1/18/2020 |
| A | Paint Walls | Inspect walls | 1/19/2020 |
| B | Add Wifi | Purchase equipment | 1/10/2020 |
| B | Add Wifi | Run cable | 1/11/2020 |
| B | Add Wifi | install repeaters | 1/12/2020 |
| B | Replace Carpet | Demo old | 1/7/2020 |
| B | Replace Carpet | replace padding | 1/8/2020 |
| B | Replace Carpet | replace carpet | 1/9/2020 |
| B | Replace Carpet | inspect carpet | 1/10/2020 |
Should result in a table like the following...
| Building | Project | Finish |
| A | Paint Walls | 1/19/2020 |
| B | Add Wifi | 1/12/2020 |
If I could also return the activity name of the last activity, bonus points!
Thanks in advance. My first post to this forum. I have found the posts here extremely helpful and the responders to be patient and polite.
7 Replies
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Project", type text}, {"Activity", type text}, {"FinishDate", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Building"}, {{"Last Date", each List.Max([FinishDate]), type datetime}}), Joined = Table.Join(Source, "Building", #"Grouped Rows", "Building"), #"Added Custom" = Table.AddColumn(Joined, "Custom", each [FinishDate]=[Last Date]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"FinishDate", "Custom"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Last Date", type date}}) in #"Changed Type1"Hope this helps.
Thank you! That was fast. I'm off to apply logic to procuction tables which have many more columns. I will reply back and mark as Solution if this does it.
Update... client just asked for the last 2 Projects to finish per Building. For reference, there are hundreds of Projects per Building in their schedule. This is Critical Path and Next Critical Path in scheduling terms.
Downloaded the pbix file. Thank you again. Off to play with it... this is different approach using RANK.
