Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |