Filtering Out the Current Month with Google Data Studio

Recently I had a task to visualize data in Google Data Studio. Lots of charts and tables with data grouped by month. But I needed to exclude the data for the current month from the dashboard.

I wanted to solve this task without changing SQL queries, creating views, and so on. In short, to do everything with Google Data Studio alone.

So, the month is specified as a date — the first day of each month:

And I needed to remove the date that belongs to the current month when displaying the table.

The first thing that came to mind was to create an additional field (cur_date) that contains the date of the first day of the current month as a formula:

DATE(YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE()), 1)

And to create one more field that would determine, using a CASE expression, whether the date in the period field equals the date in the current-month field cur_date. It should look like this:

CASE
  WHEN period = cur_date THEN 1
  ELSE 0
END

But this approach didn’t work, because in CASE you can’t use conditions that compare two fields with each other.

Then another idea occurred to me — to take the difference between the date of the given period and the date of the current month cur_date:

ABS(YEAR(CURRENT_DATE())-YEAR(period))+ABS(MONTH(CURRENT_DATE())-MONTH(period))

If the difference equals zero, the record belongs to the current month, and it can be filtered out. If it’s greater than zero, this is data from past periods, and we display it on the chart or in the dashboard table.