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 [email protected].