A new Period-over-Period (PoP) measure is now available in Looker, making it easier to create comparison-based measures directly within your LookML models. This article explains how it works, how to use it, and the benefits and limitations to consider.

Google documentation: https://cloud.google.com/looker/docs/period-over-period

What is PoP?

Period-over-period (PoP) analysis is a pattern of analysis that measures something in the present and compares it to the same measurement in a comparable period of time in the past.

How to use it in Looker

Looker’s new period_over_period measure type simplifies this process by letting you define comparative metrics using four key parameters:

  • based_on: The base measure to compare.

  • based_on_time: The date dimension to define the time context for comparison.

  • period: The comparison window, such as year, month, or week.

  • kind: The type of comparison you want. The raw value from the previous period, the percentage change from the previous period, the difference between the current and previous value.

measure: m_nb_sessions {
    label: "Sessions"
    description: "Number of distinct sessions"
    group_label: "1. Traffic"
    type: count_distinct
    sql: ${session_id} ;;
  }

measure: m_nb_sessions_last_year {
    type: period_over_period
    based_on: dm_sessions_pages.m_nb_sessions
    based_on_time: dm_sessions_pages.date_date
    period: year
    kind: previous
    group_label: "1. Traffic"
    label: "Sessions Last Year"
    description: "Number of sessions from the previous year"
  }

  measure: m_nb_sessions_yoy_change {
    type: period_over_period

    based_on: dm_sessions_pages.m_nb_sessions
    based_on_time: dm_sessions_pages.date_date
    period: year
    kind: relative_change
    value_format_name: percent_2
    group_label: "1. Traffic"
    label: "Sessions YoY % Change"
    description: "Year over year percentage change in sessions"
  }

Important: These PoP measures will only work if a time dimension (such as date_date) is present in your Explore.

Screenshot of a Looker dashboard showing a combined bar and line chart, depicting sessions over time, sessions last year, and YoY percentage change, alongside a detailed data table for deeper analysis.


How does it work under the hood?

When you inspect the generated SQL from Looker, you’ll see that:

  1. The base measure is first computed using the specified time granularity in a WITH clause. This correspond to the based_on parameter in the LookML measure.

  2. The date dimension is used to take the value of the previous period. This correspond to the based_on_time parameter in the LookML measure.

  3. The period to compare to have previous year, previous month, previous week … This correspond to the period parameter in the LookML measure.

This makes it easier to trace how your PoP metrics are derived and ensures consistency across visualizations.

Screenshot showing generated SQL from Looker, illustrating the Period-over-Period (PoP) measure calculation logic, including date adjustments, distinct session counting, and comparative periods.

Benefits and Limitations

Pros:

  • Simple and clean syntax for creating PoP metrics.

  • Easy to use in both time series charts and tables.

  • Consistent calculations and reusable logic in LookML.

Current limitations:

  • The date dimension must be included in the visualization for the metric to work.

  • Parameters like period and kind are static. Uusers cannot change them dynamically using filters.

  • Not as flexible as other tools (e.g., Looker Studio), where PoP comparisons can be applied directly in the UI.

If you’re looking for more flexible or advanced use cases for PoP in Looker, check out the following article: https://astrafy.io/the-hub/blog/technical/effective-period-comparison-in-looker

If you enjoyed reading this article, stay tuned as we regularly publish articles on Looker and how to get the most value of this tool. Follow Astrafy on LinkedIn to be notified for the next article ;).