Completion Skyline template

Download the template for Excel here.

Edit 22-august-2018 : updated for PowerBI & Excel, you can select either precom or com, or any combination ( Phase, SP etc),  the DAX measure was done by Owen Auger 

Edit 14-may-2018 :  PowerBI now support more complex conditional formatting, the post is updated with PowerBI templates.  

PowerBI report here

PBIX 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.

skyline

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 :completion_system_export

Export from your Planning Software :p6_export

A Master Table for Dates:mst_date

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.

refresh

go to Powerquery, show pane, and refresh

refresh 

some consideration 

  • 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.  
Advertisements

17 thoughts on “Completion Skyline template”

  1. Hi this is what I have been looking for, but I can not make it work.
    I tried a couple of colleagues to use it, with no luck.
    It seems there are some hidden input there.

    Hope you can help me

    Like

  2. It is great to see more specialty planning tools on the Power BI platform. I have one question – how would you recommend modifying the RANKX formula to allow for filtering? For example, if I add a slicer that filters the Forecast table by SEPARABLE_PORTION “200”, the correct subsystems are visible, but they do not collapse to the bottom of the chart. I have tried several variations to get the subsystems to re-rank after a selection like that, but so far I haven’t been successful.

    Liked by 1 person

    1. Hi Aaron

      I added a filter for SP, and updated the pbix file, as you will notice, the formula works if you select only one SP, otherwise the rankk will not be correct.

      Like

  3. Hi, I have a bit different scenario. I need to see the skyline chart as a total without selecting Precom or Commissioning e.g. collapse to the bottom of the chart. At the same time, it should be possible to use a slicer that filters Precom – Commissioning.

    Liked by 1 person

    1. Hen

      sorry for the late reply, your scenario is doable, but I have to rewrite the calculation, I could not figure out a generic way to filter regardless of the number of dimension the end user want, PowerBI is a great modelling software, but there is a lot of works to be done for graphics

      Like

  4. Min

    Your work is excellent.

    One question. Is it possible to group the Subsytem by color per week, eg. Starting with “not yet started, Started, 20-50% etc. I have more than 200 items weekly and would be great to see items sorted by colors.

    Regards
    Hen

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s