Milestone RAG status Current vs Prev Trend Chart

[ramgouda]

Hello all,
I am using EXCEL 2007.
I am trying to create a milestone RAG status comparision trend for a programme which consists of around 10 projects.
Each project will have three levels of milestones (A,B and C). and each of this milestone has a prev-RAG status and Current RAG status with a baseline date and actual finish date.

Attached is the sample data sheet.

I tried to use pivot table charts to get the count of each RAG type for current RAG and Prev RAG aginst the three different level milestones but could not come up with a decent chart.

for e.g. in the pivot table, I have created filters for Projects and grouped the baseline date into months. If I chose a month, and a project the chart should display 'Milestone Levels' on the x-axis and plot the count of current and Prev RED, AMBER and GREEN for each of these levels.

Could any one help please? I am starting to think to use VBA code instead of pivot tables but struggling. Any help would be much appreciated.

Thanks