Reports: Filter based on Current Month

Giles Magee

Hello,

One of our clients has a sheet which tracks data by month for a number of regions. They would like to display only the current month's data on an Executive level Dashboard, therefore displaying the data for all the regions together as a snapshot for the current month.

My thought was to create a report and then filter by the current month, but there is no option to do that.

Does anyone have any ideas how best to achieve this? I can add additional columns to the Sheet if that helps bring a filter in, but the client obviously does not want to have to go into the report to change filter criteria every month.

I hope that makes sense. If not, please let me know.

Many thanks in advance for all your help.

Giles

Kelly Moore

Hey @Giles Magee

A solution I often use is with a checkbox helper column that I call IsThisMonth. I use this column formula in this helper column

=IF(MONTH(TODAY())=MONTH([whatever date column you have]@row), 1)

You can then use a report to filter using your IsThisMonth column.

Depending on how frequently your spreadsheet is touched, you may need automation to keep the TODAY() function synced with the date. This screenshot may help you set that up

cheers

Kelly Moore

Hey @Giles Magee

A solution I often use is with a checkbox helper column that I call IsThisMonth. I use this column formula in this helper column

=IF(MONTH(TODAY())=MONTH([whatever date column you have]@row), 1)

You can then use a report to filter using your IsThisMonth column.

Depending on how frequently your spreadsheet is touched, you may need automation to keep the TODAY() function synced with the date. This screenshot may help you set that up

cheers

Giles Magee

Thank you very much, @KDM.

As I was falling asleep last night I did have the thought that I might be able to use a checkbox to determine whether it is MONTH(TODAY()), but your answer takes it even further.

I really appreciate it.

Giles