Hi All,

In putting together an Excel-based Loss Event Logger application, one of the reports required was to display a rolling history of losses by month per loss category.

I wanted to avoid the situation where an operator had to calculate and enter a date range so needed to provide a formula-based automatic range selection for an entered number of months. The two ranges required were:

1) 12-months (A Year-At-A-Glance)
2) 13-months (As for 1) plus an additional month to provide last month to first month comparison

The attached example shows how this was done.

Rolling N-Month Pivot Table.xlsx

In the source data table, an extra column was created in which a formula compares the event date to the date range selected and injects one of two values ("In" or "Out"). This field is then used at Report Filter level to only include events within the required range (12 or 13 whole months).

Some other "nice-to-haves" included:

a) Selectable Excessive Loss Amount highlighting within the Pivot Report
b) Trend lines across the date range per category (achieved using SparkLines)

The user department manager just loves this one...

Best Regards,

Dave