Gantt Chart in PowerPivot

Guest post by David Churchward [Twitter]

Gantt Chart in PowerPivot

Gantt Chart in PowerPivot, fully dynamic and sliceable!

It’s a rare diversion from the normal Financial stuff that I subject you to!  Having run numerous projects and found myself writing endless task lists in Excel so that I can distribute to other people, it suddenly occurred to me that PowerPivot can do this.

Not only can PowerPivot do Gantt Charts, I think it destroys other software in this game.  Sure, MS Project is always going to be there and I’m very fond of it, but, anyone familiar with this:

“Thanks for sending that through, but I don’t have MS Project.  Can you give me an Excel version or PDF”?”

Maybe you haven’t, but I find myself continuously confronted with distributing Excel task lists and producing my own Gantt charts in Excel to grab screenshots for presentations.  PowerPivot gives me (and you) the means to monitor and update tasks quickly and easily, but it also gives you the means to distribute via Sharepoint – awesome!

If you want to just get on with it, GRAB THE FILE HERE and start using it.  There’s some brief instructions on how to use it in the file.  Otherwise, read on …

How It’s Done (for those of you left)

For those of you who want to know how it’s done, here we go.

The Dataset

It’s a simple dataset.  After all, there’s only so much information you can collect on a project,right?!

Gant Chart Data Structure

I have a tasks table (shown below) with the standard task type elements.  This isn’t linked to anything!  It stands alone entirely.  Since I want to manage and update my tasks within Excel, this is a linked table.

Gant Tasks Table

You’ll notice the usual sort of information relating to a task such as start and end dates, description, categories, who’s responsible and how far complete the task is.

I then have a dates table which is pretty standard (but you can get help with this HERE or just download the file) and a separate days table.  The days table is more of a “settings thing”.  I want to provide the means to determine which days are to be shown (ie Monday to Friday or a full week) and how I want to display the days on my report.

Dates Calculated Columns

I’m collecting some pretty rudimentary date in my tasks table.  I need to turn this into something that PowerPivot can use easily.

Gant Task Table in PowerPivot Window

Status

I have dates (Start and End) and a percent complete field (Pct_Complete) in my table. The combination of these elements tell me whether the task is complete, overdue or just outstanding.

=IF(

Tasks[Pct_Complete]=1,”Complete”,

IF([End]<TODAY() && Tasks [Pct_Complete]<1,”Overdue”

,”Outstanding”

     )

      )

This is pretty much a standard Excel nested IF statement but written as a calculated column.  It’s as simple as saying “IF Pct_Complete = 1 (ie 100%) then mark as complete.  Otherwise, if my End date predates todays date AND (&&) the project isn’t complete then mark as overdue.  Otherwise, mark as Outstanding”.

Multiplier

I’ll come on to why I need a multiplier shortly, but I essentially need to translate my Status value into a number.  This could essentially be any three numbers.  I plumped for 2 = “Complete”, –1 = “Overdue” and “Outstanding” (ie everything else) = 1.

=SWITCH(Tasks[Status],”Complete”,2,”Overdue”,-1,1)

Percent

The Percent field is a translation of the Pct_Complete field.  I simply want to be able to display the value on my Pivottable in a user friendly format, i.e without any blank values and with 10% represented as that – not 0.1.

=IF(Tasks[Pct_Complete]=BLANK(),0,Tasks[Pct_Complete]*100) & “%”

Another pretty standard Excel type IF statement where blank values are turned into 0 and everything else in multiplied by 100 to give a percentage value, suffixed with a “%” sign.

Dates Table

Gant Dates Table

I have 4 calculated columns in my Dates table.

Week Ending

I want to group my dates into a week ending date. I’ve chosen to use Friday as my week ending.

=DATEADD(Dates[Date],RELATED(Days[Day_Add]),DAY)

The DATEADD function carries the syntax =DATEADD(Dates, Number of Internals, Interval).  I therefore need to present my date value, tell it how many intervals to increment and then tell it what what type of interval to increment by (ie days, months, years etc).

The RELATED function pulls a value from my Days table where I’ve set a field called [Day_Add] to provide this value.  For example, if I want Friday as my week ending value, I need to add 5 days to a Sunday to get to my Friday value.

Days Table

This is a table where I can set how I want my dates to behave.  It presents a Day_Show field showing how I want the day of the week to be displayed.  In my example, I just use the initial character.

The Include field is essentially a flag where I can switch weeks days on and off.  In this example, I’m showing Monday to Friday, with Saturday and Sunday essentially switched off”!

Gant Days Table

I won’t labour the other stuff because I think it’s fairly self explanatory and we want to get on with the good stuff – the proper DAX!

The Gantt DAX

My final report will have dates on columns and tasks on rows.  For each task, I want to plot a symbol against the dates to which the task relates.

To do this, I need a measure that will return a value of 1 where the task relates to the date.  I use the following measure which I’ve imaginatively called “Gant”! (although I obviously couldn’t spell at the time as it should have been Gantt)

=IF(

COUNTROWS(VALUES(Tasks[Description]))=1,

IF(LASTDATE(VALUES(Dates[Date]))>=LASTDATE(VALUES(Tasks[Start]))

&&LASTDATE(VALUES(Dates[Date]))<=LASTDATE(VALUES(Tasks[End]))

&&MAX(Dates[Include])=1,

CALCULATE(

COUNTROWS(VALUES(Dates)),

DATESBETWEEN(Dates[Date],MAX(Tasks[Start]),MAX(Tasks[End]))

      )

   )

) * MAX(Tasks[Multiplier])

It’s a pretty simple “IF” statement really.

    1. I only want to evaluate where I have 1 description.  That is to say that I’m not interested in any other aggregation as it probably doesn’t make sense.  Therefore, I set COUNTROWS(VALUES(Tasks[Description]))=1
    2. I then determine whether the date on columns is between the Start and End date on my task using

LASTDATE(VALUES(Dates[Date]))>=LASTDATE(VALUES(Tasks[Start]))

&&LASTDATE(VALUES(Dates[Date]))<=LASTDATE(VALUES(Tasks[End]))

  1. I finally tell my measure to only include dates that I want included, i.e where MAX(Dates[Include])=1
  2. Where all of these conditions hold as true, I use a CALCULATE function to COUNTROWS from my Dates table where the date is between my Start and End Date.  Since I have one date on each column, I can only ever return a maximum value of 1.  That is to say that it’s either a valid date for my task or it isn’t!

Since I’m only going to return a 1 where the date and task combination is valid or a blank where it isn’t, I can multiply by my Multiplier to tell my measure whether the task is Complete, Overdue or Outstanding.

Gant Pre Conditional Formatting

The Conditional Formatting

Now we’re in to standard formatting “stuff”.  I’m returning values of 1, 2 or –1 depending on the status of my task and when it’s valid.  I can turn this into symbols with the settings in the screenshot below.

Gant Conditional Formatting

You’ll notice that my multiplier value is determining which symbol gets applied and I’ve set the “Show Icon Only” to ensure that all I see is a symbol.

With the usual formatting and insertion of slicers, the job is done!

One more task – Upload it to SharePoint

Of course, to get the real value out of this, let those involved in the project see it.  Upload to SharePoint and they can.  Not only that, they can interact with it.  All this capability from a simple upload!

Gant Chart in SharePoint

Just one point to note – the “Status” calculated column uses TODAY() to evaluate dates against today’s date.  As this is a calculated column, it is only calculated on a data refresh.  Therefore, if you don’t refresh the file, this calculation will be incorrect as soon as tomorrow!

When I upload to SharePoint, I always set a daily refresh so this isn’t a serious consideration for me.  However, if you can’t set daily refresh, you’ll need a measure as opposed to a calculated column – something I’ll go into in a future post if there’s call for it.

It’s a Community Project Thing!

There’s loads that can be done with this.  I’m aware of some of the weaknesses that need to be overcome such as:

  1. Why do we need a task for the start and end of the project?  This is because we don’t want to lose any valid dates on our report where there isn’t any activity.  I know we can do this in DAX without needing the record but I haven’t got around to it.  Check out the file and you’ll see what I mean.
  2. Task dependencies – I don’ t have any in this model.  Maybe we don’t need them.  I’m not sure yet.
  3. Task ordering – I haven’t done any of this yet.  I know we can add a “Sort By” slicer but is it required.

I use this type of file for presentations and pushing tasks out to those who need to see them.  I would welcome your input on where to take this file now.  Maybe it’s all that it needs to be.  However, if you want to add to it or send me your thoughts on where it should go, please feel free to comment or drop me a line at david.churchward@azzu.co.uk.

18 Responses to Gantt Chart in PowerPivot

  1. Jonas says:

    Hello there!

    First of I must say that this is an excellent blog and reading it has become a part of my morning routine together with that first cup of coffee that tastes so great.

    This solution could be very interesting for a client of mine but they are currently working with PowerPivot v1 and sharepoint 2010. Is it possible to build this functionality i powerpivot v1? I’d like to know if some functionality that is exclusive for v.2 was used so I don’t run into that brick wall later :)

    // Jonas W.

    • David Churchward says:

      Hi Jonas

      Thanks for your comments. This should work perfectly well in V1. The only item that you’ll probably need to change is the Multiplier. In that formula, I use SWITCH which isn’t available in V1. You can use a simple IF statement instead.

      I hope it all works well for you.

      Cheers
      David

    • powerpivotpro says:

      Jonas, I wanted to add: that this blog has become so closely associated for you with that sacred moment known as First Coffee… this is one of the best things I have ever heard :)

      Might I also add, however, that virtually ANYTHING would benefit from regular association with First Coffee. I might even learn to enjoy country music if it were played every day during First Coffee.

      So I hope the blog earned the right for First Coffee honors, rather than being positively appreciated by its chance association with the benevolent ritual.

      Now if you will excuse me, Second Coffee is calling.

  2. Vegard R says:

    Hi!

    Great Gantt Chart in PowerPivot!
    I am trying to estimate the total workload for different persons (and other dimensions/resources) in a project. And this Gantt Chart with some adjustments should possibly make it possible.

    I have added a new field BudgetedHours, and I also calculate Activity Length based on the dates. Then I calculate a daily ResourceLoad for each task. (BudgetedHours/ActivityLength) And it all shows pretty in the Gantt Chart with the ResourceLoad (Average hours for each day) for each task on the correct dates. But it seems I can’t aggregate the total sum of ResourceLoad for one person, if one person works on more then one task each day.

    I have also removed this COUNTROWS(VALUES(Tasks[Description]))=1 in the Gant measure because i want it to be able to aggregate for Persons and other dimensions.

    Any ideas on how to make this great Gantt Chart also show workload and aggregate it for different dimensions,and not only show status?

    - Vegard R

  3. Vegard R says:

    Hi again

    To be more precise, it is not the total workload i am after, (I already know that) but the workload on each separate day, and the possibility to aggregate this up to different dimensions.

    - Vegard R

    • David Churchward says:

      Hi

      Sounds like a great idea. Are you able to send me the workbook and we can collaborate on this one? I would suggest that we need a separate measure altogether that sits alongside the Gant measure.

      You can get me at david.churchward@azzu.co.uk

      Thanks
      David

  4. Tim Rodman says:

    This is a great workbook! I haven’t done much project management, but I see how this tool could be useful for the project management team at our company.

    I like the idea of distributing it through Sharepoint. I wonder if anyone has any thoughts though about storing the data on the Excel tab versus storing in a Sharepoint list and pointing PowerPivot to it via a Data Feed.

    Regarding storing the data on the Excel tab, I could see there being advantages when connectivity is an issue. Our company digs tunnels in remote parts of the world and there isn’t always an internet connection at the jobsite. In this scenario I see an advantage in allowing the project manager to manage the data locally on a laptop and then uploading the updated file to Sharepoint later. They could even check the file out in Sharepoint to make sure they get a lock on the file.

    Regarding storing the data in a Sharepoint list, I could see there being advantages when multiple people are updating data on the project. The Sharepoint list security could be set so that each person could only manage their own tasks. Then the Excel file would aggregate everyone’s data together for viewing purposes.

    • David Churchward says:

      Hi Tim

      Storing / collecting the data in the Excel workbook was a convenience thing to distribute the workbook. Any serious intention for this analysis should, in my opinion, be served by a controlled system and there’s no reason why SharePoint couldn’t be that vehicle.

      For lower level project management and simple task management, the Excel data collection method may suffice. In addition, remote working may require some flexibility here as you mention.

      I think it’s a “needs must” scenario, but where possible, in my opinion, get a proper database system to serve the data. I wonder if we can hook up to MS Project – ha! Might defeat the object though…

      Thanks for your comments.

      David

  5. David Hager says:

    FWIW, here is a WeekEnding DAX formula that works differently from the method that David uses here.

    =CALCULATE(MIN(Table1[DATE] ),FILTER(ALL(Table1),FORMAT([Date],”dddd”)=RELATED(LDOW[LDOW]) && [Date]>= EARLIER([Date])))

    LDOW is a 1 row linked table with a formula to a cell in another sheet that contains a Data Validation list with the days of the week.

  6. Jonas says:

    Hi again,
    Inspired by this post I’ve started to work on a simple implementation of the powerpivot gantt chart. Our needs differ somewhat from the typical gantt chart. Rather than visualizing a period of time when work is planned we want to have three different icons for different milestones in a project.

    The grain of the chart is weekly and using different icons for different milestones was easy but my problem is that when the user filter for a specific project only the weeks were a milestone was reached shows up in the chart. I am looking for a way to make the chart always show week 1 – 53. Please have a look at the this picture for clarification: http://sdrv.ms/PbqOam
    The gantt measure looks as follow:

    =COUNTROWS(VALUES(ProjectActivities[Project])) * MAX(ProjectActivities[Multiplier])

    The only timestamps I have on the project is the duedate of the milestone and I’ve simply connected it to DimDate and then put weeks on the columns area.

    • Jonas says:

      Turns out the solution was quite simple. PivotTable Optionas -> Display -> Show values with no data on colums radio button.

      Thought I might as well post it here if someone else has the same problem.

      // Jonas W.

  7. Alexander says:

    First of all thanks a lot David for writing this great tutorial/guide to Gantt in powerpivot. I went from a total dax beginner to understanding the basics of it with your text as a general guide as I looked at various other sources for a more indepth understanding of the dax functions.

    I really like the way you formatted the pivottable, but am struggling to reproduce it. Specifically I wonder:
    1. How do you prevent the resizing from happening when you change the filters with the slicers
    2. How did you color the different catagories?
    3. How did you get some row labels to be in the same row (description, start, end, etc.) and others seperate (Category 1 and 2)?

    Furthermore for those who are interested in a dax solution for a slightly different data situation, read on! I had a task (dates of courses actually, but for consistency purposes i will use tasks instead) with multiple dates, sort of like a repeating task, which was datadesigned like this:
    TaskInfo: TaskName(PrimaryKey), TaskInfo (general info)
    TaskDates: TaskName(ForeignKey), StartDate, EndDate, (could be expanded by task specific stuff like who is responsible)

    The goal was to make a Gantt chart that would join all the various dates per TaskName. Dates are allowed to overlap, I want to know when a task starts and when it is being overlapped. I solved it with the following dax measures:
    // If the current date is a start date it will return -1
    // otherwise it will return the amount of overlapping dates
    CALC_GANTT:= IF([CALC_StartAmount]>=1,-1,[CALC_BetweenStartEnd])
    // Calculates the amount of values of taskdates that are matching the current date
    CALC_StartAmount:= CALCULATE(COUNTA(TaskDates[TaskName]),
    Filter(TaskDates, [LKP_Date] = TaskDates[StartDate]))
    // Looks up the current date from a datestream
    // (see: http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/
    // I had trouble finding this the first time I saw your date table)
    LKP_Date:= LASTDATE(Dates[DateKey])
    // Calculates the amount of values that have a start date and an end date
    // that is overlapping the current date of the datetable
    // Example: startdate = 1 jan 2013, enddate = 10 jan 2013, datekey = 5 jan 2013
    // Then it will be true because: 1 = 5
    CALC_BetweenStartEnd:= CALCULATE(COUNTA(TaskDates[TaskName]),
    FILTER(TaskDates, TaskDates[StartDate] = [LKP_Date]))

  8. Pieter says:

    This post inspired me to start a project to display the overnight executiontime of all the SSIS packages in our datawarehouse. So in this case I have to deal with a start time and en end time of the packages instead of an startdate and an enddate of a task.

    I have added an time dimension table to power pivot for every quarter of an hour in a 24 hour time period. But in this case I can’t use the datesbetween function to calculate te gannt value because the dates will be moving from day tot day. I think I will have to calculate it based on the time dimension an see if the key value of a certain quarter of an hour falls between the starttime_key and the endtime_key of the package. But I got stuck with it.

    I did something like this:
    gannt:=calculate(countrows(values[DimTimeOfDay]),AND(MAX(Doorlooptijden_packages[StartTimeOfDay_key]),MAX(Doorlooptijden_packages[FinishTimeOfDay_key]))

    But I get all kinds of syntax errors.

    Any suggestions would be appreciated

  9. Mahen says:

    This is very impressive and amazing. Hats off!! I was wishing for a solution like this in excel. I have Power pivot v1 and so, I could not use the file above. I see there is updates done to the original model to include hours and if possible, can you please suggest where and how I can download the latest file . Also, I can convert my v1 to v2, but I am afraid of losing any of my old files during the upgrade. One website I saw mentioned to uninstall v1 before installing v2 and I am really afraid of losing data. Do we lose data in this process? Any advice?. Thank you so much for the help.

  10. ivan says:

    I just found this nice implementation :)
    I am new to the Power Pivot app. I can’t even find any of the programming logic anywhere in the downloaded Excel workbook. Can you please guide me where to look for it.

  11. FM says:

    Great post and very useful. I started using the Gantt as a schedule rather than a project management tool. I manipulated it slightly to display week numbers instead of weekdays. This turns up a small problem: some values are not showing on the pivot table at all. In many cases, these are values which are less than 1 week in duration. But it is not consistently so. It seems to be the case for repeat events e.g. if the name of a traveler under a common category, no value appears in the table. Do you know of a neat way to solve this kind of problem? Another question again relates to repeated events and the possibility to use the pivot table to roll up from the individual record to a category level e.g. if I schedule four Office Meetings individually over the year, it would be nice to roll up to a summary line called ‘Office meetings’ and see the schedule for the four on one line rather than four separate line. Currently, there are no summary value appearing in the table at all. I’m hoping you can suggest a small trick to work around it. Thanks.

  12. Nick says:

    First off… Awesome post!

    FM, I am trying something simular. I am building a “3 Year Road Map” model for our IT Department. In our case we have a “project” that can have multiple stages; New Service, Maintenance, Upgrade, Retire. Each stage happens over a period of time. Things look perfect with each on a line yet when I try to summarize the whole “life span” under just the “project” name only the last entry shows. Were you able to figure something out?

    • Bryan says:

      I’d be interested in the “multiple project phase single line gantt chart” as well. I’ve just received a request from our VP to produce a spreadsheet listing all of our capital projects (over 200) with a single row gantt chart broken down by phase for each project. I’m not allowed to use my scheduling software (Oracle Primavera P6) which is perfect for stuff like this.

Leave a Comment or Question