Filtering on the Last Day of the Month

[sam8]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Filtering on the last day of the month is very simple with the right logic. The key is to think of the last day of the month as the day on which you can say, "tomorrow is the first day of the month."

Method 1: Custom Filter

To show only dates that are the last day of the month, we can use a custom filter:

extract_days(add_days(1,${date})) = 1

The above expression returns true if "tomorrow is the first of the month," which is another way of saying "today is the last day of the month."
 

Method 2: LookML

We can take the logic of the custom filter above and convert it to SQL in a type: yesno dimension. This example uses Redshift:

dimension: is_last_day_of_month {
  type: yesno
  sql: EXTRACT( day from DATEADD(day,1,${date_raw}) ) = 1 ;;
}

Here we use the raw timeframe, because it directly inserts the database column without any extra formatting.

SQL For Various Dialects

The SQL you use in the dimension varies by database dialect. The example above was written for Redshift. Here are the SQL expressions you can use with several other dialects:

• For PostgreSQL, use EXTRACT( DAY FROM (NOW() + INTERVAL '1 DAY')) = 1
• For Google BigQuery, use SELECT EXTRACT( DAY FROM DATE_ADD(${date_raw}, INTERVAL 1 DAY)) = 1
• For MySQL, Oracle, Teradata, and MariaDB, you can use the built-in LAST_DAY() function instead