Calculated critical path table using two columns with MAX(Finish) of a third date column

[seaborne]

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.

[seaborne]

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.

[Ashish_Mathur]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[seaborne]

Apologies. The desired result is to show the last "N" projects to complete per building, with the last activity for each project (display this optionally - not a current requirement). The rankx solution with a filter applied for "N" is a good solution. But the attached pbix file appears to rank the last two activities within each Project. It shows two activities for the same Project per building. I only want to show the last activity per Project. The ranking should be at the Project level.

Building	Project	Activity	Finish
A	Paint Walls	Inspect walls	1/19/2020
B	Add Wifi	install repeaters	1/12/2020

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[seaborne]

Downloaded the pbix file. Thank you again. Off to play with it... this is different approach using RANK.

[Ashish_Mathur]

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.

[seaborne]

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.