I have a set of MS Project-originated schedule data for many projects. Each project has 6 dates (and each date has its own column):
- Estimated start/end dates
- Baseline start/end dates (i.e., targets/goals)
- Actual start/end dates (i.e., performance - what I want to measure against the baselines)
I was able to set up a gantt chart with projects on the Rows shelf, and DAY(Design Baseline Start) on the column shelf. The size is based on the # of days duration between the Baseline Finish and Baseline Start Date.
Where I'm running into trouble is adding reference lines for the other dates. I was able to do it for the Baseline Start Date, by setting the value to DAY(Design Baseline Start) -- the same field that determines the axis:
More importantly than that is to be able to add a reference line for the Actual Finish date, so we can see which projects ended late. I tried creating a parameter based off of the Design Actual Finish field, but then realized parameters can only be displayed for 1 value at a time. I would like the reference line to reflect the Finish date that is unique to each row/record/project. This is what I have in mind:
Some of my thoughts for possible solutions include--
- Is it possible to create a parameter that includes an "All" option, and then base a calculated field off of the parameter? (I'm new to parameters, by the way).
- A dual axis of some sort, combining my existing gantt chart with lines corresponding to each Actual Finish date
- Setting the duration to count the # of days between the ACTUAL finish date and the baseline start date - then using some sort of coloring to distinguish the portion of each project's duration that occurred within the baseline dates, and which portion occurred between the baseline and actual finish dates
- Some sort of a waterfall chart, where each project has two horizontal bars on top of each other- 1 corresponding to the baseline duration (as it is now), and the other showing the actual duration
- Bullet graph instead of a gantt chart (since we are comparing actuals to goals- but can bullet graphs work with a time-based axis?)
- Add a separate data source that acts as a master calendar of all dates -- base the axis off of that list of dates, rather than 1 particular date field??
- Restructure the data using a UNION ALL statement so each project appears in multiple rows
I'm hoping someone who reads this will have a better idea how to achieve this, or at least can tell me which of those ideas would never work I've attached a packaged workbook that contains a set of dummy data for a limited number of projects (FYI-- right now I'm only testing the Design phase, but each project also has another set of 6 dates for 4 other phases! Once I get past this, I'll try to figure out how to deal with the other data as well...)
Thank you in advance for your help!
Gantt Test.twbx.zip 54.8 KB