Period-over-Period Analysis in LookML: Templated Filters and Dynamic Comparisons

Almost every business stakeholder eventually asks the same question: how does this period compare with the last one? Looker's built-in table calculations can answer it for a single query, but they do not travel well into dashboards, and hard-coded measures such as revenue_last_month do not flex. This tutorial shows a reusable LookML pattern that lets users pick any date range and automatically compare it with the equivalent prior period.

The approach

The pattern has three pieces:

1. A filter-only field where the user picks the current period.
2. Hidden dimensions that classify each row as current or previous using that filter.
3. Measures filtered on that classification, plus a change measure.

Step 1: A filter-only field

view: orders {
  filter: current_period {
    type: date
    label: "Current Period"
    description: "Choose the period to analyze. The comparison period is the same length, immediately before."
  }
}

A filter field does not map to a column. It only provides a value that other fields can reference.

Step 2: Classify rows

Use {% date_start %} and {% date_end %} to read the chosen range, then compute the previous range of equal length.

  dimension: period_bucket {
    hidden: yes
    type: string
    sql:
      CASE
        WHEN {% condition current_period %} ${created_raw} {% endcondition %}
          THEN 'current'
        WHEN ${created_raw} >= TIMESTAMP_SUB(
               TIMESTAMP({% date_start current_period %}),
               INTERVAL TIMESTAMP_DIFF(
                 TIMESTAMP({% date_end current_period %}),
                 TIMESTAMP({% date_start current_period %}), DAY) DAY)
         AND ${created_raw} < TIMESTAMP({% date_start current_period %})
          THEN 'previous'
      END ;;
  }

The example uses BigQuery syntax; swap in your dialect's date-difference functions as needed. Note that date_start and date_end return null when the user chooses an open-ended filter, so guard against that (see pitfalls).

Step 3: Measures

  measure: revenue_current {
    type: sum
    sql: ${sale_price} ;;
    filters: [period_bucket: "current"]
    value_format_name: usd_0
  }

  measure: revenue_previous {
    type: sum
    sql: ${sale_price} ;;
    filters: [period_bucket: "previous"]
    value_format_name: usd_0
  }

  measure: revenue_change_pct {
    type: number
    sql: SAFE_DIVIDE(${revenue_current} - ${revenue_previous}, ${revenue_previous}) ;;
    value_format_name: percent_1
  }

Step 4: Constrain the query

Because the previous period lies outside the user's filter, the query would normally scan only the current range. Add a sql_always_where or, better, an Explore-level always_filter so the scan covers both windows and users cannot forget the filter:

explore: orders {
  always_filter: {
    filters: [orders.current_period: "last 30 days"]
  }
  sql_always_where:
    ${created_raw} >= TIMESTAMP_SUB(
      TIMESTAMP({% date_start orders.current_period %}),
      INTERVAL TIMESTAMP_DIFF(TIMESTAMP({% date_end orders.current_period %}),
        TIMESTAMP({% date_start orders.current_period %}), DAY) DAY)
    AND ${created_raw} < TIMESTAMP({% date_end orders.current_period %}) ;;
}

Common pitfalls

• Open-ended filters. "before 2025-01-01" has no start date. Wrap the logic in {% if current_period._is_filtered %} or require a bounded range with required_filters/documentation.
• Timezones. Make sure the filter and the column are evaluated in the same timezone; see convert_tz settings.
• Partial periods. Comparing a month-to-date with a full previous month is misleading. Equal-length windows avoid this, but you may prefer year-over-year alignment for seasonal businesses.
• Partition pruning. Check the generated SQL in the SQL tab to confirm the warehouse prunes partitions for both windows.
• Dashboard wiring. Connect the dashboard filter to current_period, not to the date dimension, or the previous window will be cut off.

Testing

Add a LookML data test asserting that revenue_previous for a fixed historical range equals a known figure, so refactors do not silently shift the comparison.

Wrapping up

With a filter-only field, a classification dimension and a few measures, you give business users one control that drives every comparison on a dashboard. If your period-over-period logic is scattered across table calculations, Vistelio's senior Looker developers can help consolidate it into the semantic layer.