Monthly Rolling report

Tim A

Hi,

I'm looking to create a report that has a filter based on the previous calendar month.

Currently I have to change the filter on a report each month and let the users know the previous month's activity is ready to view.

I'd like to be able to do something automatic so on the 1st of each month the report changes and users can see the previous month without me having to update filters.

I've seen a few posts about rolling reports involving formulas which I can't get my head around so any help is much appreciated.

Paul Newcome

You would insert a checkbox type column on the source sheet(s) with a formula along the lines of

=IF(AND(IFERROR(MONTH([Date Column]@row), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR([Date Column]@row), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)

The above should check the box on every row that is in the previous month based on today's date. You would then set your report filter to only show rows that are checked.

harryjohn

You can achieve this by using a dynamic date filter that automatically adjusts to show the previous calendar month. Most reporting tools support date formulas—look for options like "Previous Month" or use a formula such as LAST_MONTH() depending on your platform.

This way, the report updates automatically on the 1st of each month, and you won't need to manually change the filter.