Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Hi,
You may download my solution PBI file from here.
Hope this helps.
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 |
Hi,
You may download my PBI file from here.
Hope this helps.
Downloaded the pbix file. Thank you again. Off to play with it... this is different approach using RANK.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
72 | |
70 | |
37 | |
29 | |
26 |
User | Count |
---|---|
91 | |
49 | |
45 | |
38 | |
36 |