Duplicate a simple table with a Pivot Table

Hi Excel heroes,

Please help me create a pivot table. It needs to show a rolling sales for 1 week, 4 weeks and 13 weeks (as well as a few simple metrics). Every new week new data gets added, the pivot must look back in those rolling increments. So that at work on Feb 4th, it looks back at weekending Feb 2nd.

I guess it has something to do with Calculated Fields but I can't figure it out.

I have attached the sample file and how the table would look.

Thank you in advance!

Quick Question: By prior week, do you mean week ending last Saturday or the 7 days prior to the current date?

Never mind. You want week ending. I can see that by the title.

Many times you can solve pivot table issues with helper columns on the source data.

First of all, I converted your data into an Excel Table. There are two reasons for this:
- Excel tables "know" how big they are, so you do not have to change the pivot table data source range. All you need to do is refresh the pivot table when you add new data.
- Excel tables copy down formulas automatically. This means that the helper column formulas will copy down when data is added.

First I started with some calculations.
Cell B1 has the current day
Cell C4 is the end of the prior week (Saturday)
Cell C5 is the end of the 4th week
Cell C6 is the end of the 13th week

The corresponding cells in column B are the previous Sundays (week start)

So last week is data from Week 1 Start to Week 1 End
4 weeks is Week 4 Start to Week 1 End
13 weeks is Week 13 Start to Week 1 End

I gave names to these cell values just to make the formulas easier to read.

The formulas for the three columns are:
Prior 1 =IF(AND([@WEEKENDING]>=P1_Start,[@WEEKENDING]<=P1_End),[@DOLLARS],0)
Prior 4 =IF(AND([@WEEKENDING]>=P4_Start,[@WEEKENDING]<=P1_End),[@DOLLARS],0)
Prior 13 =IF(AND([@WEEKENDING]>=P13_Start,[@WEEKENDING]<=P1_End),[@DOLLARS],0)

If the date is outside the range, no dollars otherwise use the dollar amount associated with the range.

I used these columns in the pivot table.