Download the template for Excel here.
Edit 14-may-2018 : PowerBI now support more complex conditional formatting, the post is updated with PowerBI templates.
PowerBI report here
Skyline charts are very popular in the precommissioning and commissioning phase, as in one page, you can see the remaining subsystems to be done, and how much ITRS to be completed, here is an example.
Excel does not support this chart natively, even power bi,( you can vote for this idea) , some people manually draw this chart in Excel, which is really time consuming or using complex vlookup formula, in this blog post i will share with you a template i built using powerquery and powerpivot, as you would expect that chart is generated with a simple refresh.
what data source you need
for convenience, the data source are included in the same file, i used a dummy data for obvious reason.
Export from your completion system :
Export from your Planning Software :
A Master Table for Dates:
a master table is included as you needed to aggregate the date, there is no point in showing a skyline per day, here i am using week, but you can change the formula to be per month.
go to Powerquery, show pane, and refresh
- Although I personally use P6, any Planning software is fine, basically we need only 1 forecast date by subsystem per phase, if the subsystem is spread in multiple PCWBS, then use the Maximum date
- P6 here is used only for the forecast date, the actual data ( ITR and completion date) are from the completion system , in some cases, i overwrite P6 forecast date if it is earlier then the completion subsystem cut off date, if P6 does not provide a date for a particular subsystem, I use the latest date from the equivalent system.
- in the slicer Phase, select either Precom or Commissioning, if you select both, th skyline will not work properly.