How to Keep Only the Data up to the Last Month (Excluding This Month)

Suppose you have historical data and want to filter data up to the last month and exclude this month. To do so, you can use aggregate function Max along with earlier than operator in a date filter. Here is how to do it.

Filter with Max Aggregate Function

On a filter dialog, select earlier than as Filter Operator, Month / Year as How to Set Value. Then select Max as Summarize Function.

this creates a filter below behind the scene.

floor_date(date, unit = "month") < max(floor_date(date, unit = "month"), na.rm = TRUE)

So what it does is:

if the month, which is calculated with floor_date(date, unit = "month"), is earlier than the last month (i.e. Jan 2020), which is calculated with max(floor_date(date, unit = "month")), keep the data“.

Please note that to get the last month, you need to use Max as the Aggregate Function instead of Last function. The reason is that the Last function just returns the value from the last record and it does not mean the value is the last Month (especially if the data is not sorted). Anyways, after the filter is applied, only data up to the Last Month (Dec 2019) remain like below.

R Package References

lubridate

For your reference, floor_date() takes a date-time column and rounds it down to the nearest boundary of the specified time unit, in this example month. The floor_date function is from lubridate package.