At first thought, we might want to create a measure of a maximum or minimum date by using a measure of type: max or type: min. However, these measure types are only compatible with numerical fields. Instead, we can create a measure to capture a maximum or minimum date in Looker by using a measure of type: date and wrapping the date field referenced in the SQL: parameter in a MIN() or MAX() function.
Example:
Suppose you had a type: time dimension group, called updated:
dimension_group: updated {
type: time
timeframes: [time, date, week, month, raw]
sql: ${TABLE}.updated_at ;;
}
We can create a measure to capture the maximum date of this dimension group as follows:
measure: last_updated_date {
type: date
sql: MAX(${updated_raw}) ;;
convert_tz: no
}
Note again that we apply the MAX() function in the SQL: parameter of the measure, and also that we are not using a measure of type: max. We're also applying convert_tz: no. This is because the timezone conversion has already occurred in dimension_group: updated, and we don't want a double conversion in the measure. Read more about the convert_tz parameter in Looker's documentation here.
You may also notice that we are referencing the ${updated_raw} time frame instead of the ${updated_date} time frame. This is because the value returned from ${updated_date} is a string, so we need to to use ${updated_raw} to reference the actual date value instead.
Max and Min Measures for a Datetime Column
Computing the maximum for a type: datetime column is a little different. In this case, you want to create a measure without declaring the type, like this:
measure: last_updated_datetime {
sql: MAX(${TABLE}.datetime_string_field) ;;
}