How to create a Pivot Graph that only looks at the Day in Month instead of Full Date?

Question

I have an Excel spreadsheet that is defined the same way as in this question.

01/10/2010 0.99
02/10/2010 1.49
02/10/2010 0.99
02/10/2010 0.99
02/10/2010 0.99
03/10/2010 1.49
03/10/2010 1.49
03/10/2010 0.99

It is a sales record, and I have several sales every day.

What I need is to display a graph where each series would be one month. I would like the curve to represent the cumulative sum of sales during said month.

I would hence be able to compare the current month to previous months, and see when I am doing best in sales.

The graph would have over the X axis numbers from 1 to 31, the Y axis would be the cumulative amount of the sales, starting at the beginning of the month.

It would look like that:

Is it possible, and if so, how?

Answer 1

Here's how I've been doing it recently in Excel 2007 (YMMV):

1. To get the day of month, add a column to your source data, with formula of =DAY(datecolumn)
2. Add the original date field as column labels, then group it (right click on the values on the sheet and click group) by month
3. Add the day of month field as row labels
4. Add in the price field to the data area, as a count field. Set it to show values as running total based on the day of month field created in step 1

Then draw a pivot chart of the table and all should be shiny.