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.

Fordialects that support period-over-period measures, Looker developers can add PoP measures to LookML projects to enable PoP analysis in the corresponding Looker Explores.

For example, the following Looker Explore query shows the number of orders that were created in the current month, along with PoP measures for the number of orders created last year, the difference from last year, and the percentage change from last year. You can verify the year-over-year comparison by spot-checking the values. For example, the value forOrders Last Year for2012-03 is the same as the value forOrders Count for2011-03:

To add a PoP measure to a LookML project, a Looker developer must create ameasure oftype: period_over_period and include thesubparameters that are described in the following section of this page.

For example, here is the LookML for a PoP measure that provides the order count for the previous year:

  measure: order_count_last_year {    type: period_over_period    description: "Order count from the previous year"    based_on: orders.count    based_on_time: orders.created_year    period: year    kind: previous  }

This PoP measure has the following attributes:

• It is defined withbased_on: orders.count, so the PoP measure will provide data about order count from the previous time period.
• It is defined askind: previous, meaning that it provides the count value from the previous time period (opposed to providing a difference in order count from the previous time period, or a percentage of change in order count from the previous time period).
• It is defined withperiod: year, so it will provide order counts from a comparable amount of time from the previous year.

Subparameters of PoP measures

A PoP measure is ameasure oftype: period_over_period that includes the subparameters that are described in the following sections:

• based_on
• based_on_time
• kind
• period
• value_to_date

As described in theExplore queries with PoP measures section, PoP measures calculate their values based on both the PoP measure's LookML definition and the fields in an Explore query. Because of this, you should adhere to the following best practices when creating a PoP measure in LookML:

• Provide to your Explore users an indication of the PoP measure'speriod, either in the name of the PoP measure or in the measure'sdescription subparameter.
• Provide to your Explore users an indication of the PoP measure'sbased_on measure, either in the name of the PoP measure or in the measure'sdescription subparameter.

For example, the following PoP measure is namedorder_count_last_year, and a description is included to let users know that the measure provides the number of orders from the previous year:

  measure: order_count_last_year {    type: period_over_period    description: "Order count from the previous year"    based_on: orders.count    based_on_time: orders.created_year    period: year    kind: previous  }

based_on

Use thebased_on field to specify the LookML measure that the PoP measure is based on. For example, to base a PoP measure on theorders.count field, you would enter the following:

    based_on: orders.count

A PoP measure based onorders.count will provide information about the number of orders from a previous time period so that you can compare the number of sales between a current period and a previous period.

The LookML measure that you specify in thebased on field must be one of the following types of measures:

• average
• average_distinct
• count
• count_distinct
• list
• max
• median
• median_distinct
• number
• min
• percentile
• percentile_distinct
• sum
• sum_distinct

based_on_time

Use thebased_on_time subparameter to provide Looker with a time field that it can use to calculate the PoP measure values. This time field can be either of the following:

• Atime-based dimension. If you specify a time-based dimension in thebased_on_time subparameter, your users must include the exact same time-based dimension in all queries that use the PoP measure. Also, the timeframe of the time-based dimension must be equal to or smaller than the PoP measure'speriod value. For example, if the PoP measure is defined withbased_on_time: created_month, the PoP measure'speriod value can't beweek ordate.

• One of the following timeframes of adimension group oftype: time:

  • year
  • fiscal_year
  • month
  • fiscal_quarter
  • quarter
  • week
  • date
  • raw

If you specify a dimension group timeframe in thebased_on_time subparameter, the specific timeframe that you use is irrelevant -- you just need to point the PoP measure at a dimension group oftype: time so that the PoP measure can use the underlying timestamp of the dimension group. You can't specify a timeframe from adimension group oftype: duration; duration type dimension groups are not supported and will produce a runtime error in the Explore.

kind

Use thekind parameter to specify the type of calculation that you want the PoP measure to make for the previous period. You can specify one of the following values forkind:

• previous: (default) The value from the previous period.
• difference: The difference between periods (the previous period subtracted from the current period).

• relative_change: The percentage change from previous period. The percentage change is calculated by the following equation:

  $$relativeChange = (current - previous)/previous$$

period

Use theperiod subparameter to specify the PoP measure'scadence, how far back you want to jump in your comparison. For example, a PoP measure that's defined withperiod: year will show the values for the previous year. If you run an Explore query on monthly order count, theperiod: year PoP measure will show the values for the same month in the previous year, so that you can compare the order count for November 2025 to the sales count of November 2024.

Theperiod subparameter supports the following values:

• year
• fiscal_year
• quarter
• fiscal_quarter
• month
• week
• date

value_to_date

Use thevalue_to_date subparameter to indicate whether Looker should calculate the values for the PoP measure by using the amount of time that has elapsed in the current timeframe at the time the query is run. Thevalue_to_date subparameter can beno (default) oryes.

• A value ofno will assume the whole timeframe window when aggregating data.
• A value ofyes will calculate the amount of time observed in the current period and apply it to the PoP measure.

For example, with a month-over-month PoP measure that is defined withvalue_to_date: yes, if at 13:10:00 on June 6 you run an Explore query with the PoP measure and a date timeframe dimension, Looker will apply the amount of time that has elapsed on June 6 (13 hours, 10 minutes, and 0 seconds) to the calculations for each of the dates in the query. For each date, Looker will provide the values for the first 13 hours and 10 minutes.

If you had the same PoP measure that was defined withvalue_to_date: no and you ran the same Explore query on June 6 at 13:10:00, Looker would calculate the value for the PoP using all of the data that is available for each date. If you are trying to compare values from June 6 to the 6th of the previous month, be aware that since June 6 isn't over yet it's possible that there will be additional data after 13:10:00.

SeeHowvalue_to_date affects PoP measure values for an example of howvalue_to_date: yes affects the results in an Explore query.

As described in theRequirements for Explore queries with PoP measures section, when you run an Explore query with a PoP measure, Looker automatically applies the minimum timeframe granularity from the query to the timeframe that's used by the PoP measure. For Explore queries with a PoP measure defined withvalue_to_date: yes, Looker takes the smallest timeframe dimension in the query and calculates the portion of that timeframe that has passed when the query is run, and then applies that portion to all of the values for the PoP measure.

Explore queries with PoP measures

The calculation that's performed for a PoP measure is based on the PoP measure's LookML definition and also on the timeframes that are specified in the Explore query itself; the PoP measure adapts its calculation to the timeframes that are selected in the Explore query. For example, if the PoP measure is defined withperiod: year, and the Explore query contains theorders.created_month timeframe dimension, the PoP measure will calculate monthly values, comparing January 2025 to January 2024. If you want to see the yearly values, you would have to run an Explore query with the PoP measure and only theorders.created_year timeframe.

Here are some examples of how a PoP measure'speriod interacts with the timeframes that are selected in an Explore query:

• If a PoP measure is defined withperiod: year, and you run an Explore query with a quarter timeframe, the PoP measure will return values from the same quarter in the previous year (Q1 of 2025 compared to Q1 of 2024).
• If a PoP measure is defined withperiod: year, and you run an Explore query with a month timeframe, the PoP measure will return values from the same month in the previous year (April 2025 compared to April 2024).
• If a PoP measure is defined withperiod: month, and you run an Explore query with a month timeframe, the PoP measure will return values for the previous month (April 2025 compared to March 2025).

Requirements for Explore queries with PoP measures

Because a PoP measure makes calculations based on both the LookML definition of the PoP measure and the fields that you select in the Explore query, at a minimum you must include the following fields in an Explore query that has a PoP measure:

• The PoP measure.
• A time dimension that is appropriate for theperiod associated with the PoP measure. The time dimension can be included in the query either from the Explore's field picker or in the Explore's filters:
  • PoP measure queries support timeframe granularities of date or larger, such as month, quarter, or year. PoP measure queries don't support dimensions with timeframes of hours or minutes.
  • If the PoP measure is defined with abased_on_time that is a timeframe of a dimension group, then the Explore query must include a timeframe from the same dimension group that uses an equal or smaller timeframe than what is specified in theperiod parameter of the PoP measure. You can include the dimension group in the Explore itself (by selecting the dimension group from the Explore's field picker) or by filtering on the dimension group. For example, if the PoP measure'sbased_on_time value is defined with a timeframe from theorders.created dimension group and the PoP measure is defined withperiod: month, the Explore query must include a timeframe from theorders.created dimension group that is equal to or smaller than a month, such asorders.created_date. The timeframe in the Explore query must match or be smaller because, for example, you can't do a month-over-month comparison of a year timeframe.
  • If the PoP measure is defined with abased_on_time that is atime-based dimension, then the Explore query must include the exact same time-based dimension, either by including the dimension from the Explore's field picker, or by specifying a filter on the dimension. The time-based dimension must be of an equal or smaller timeframe than what is specified in theperiod parameter of the PoP measure. For example, if the PoP measure is defined withbased_on_time: created_date and the PoP measure is defined withperiod: month, the Explore query must include thecreated_date dimension.

If the PoP measure is defined with abased_on_time that is a timeframe of a dimension group, note the following requirements for the timeframe in the Explore query:

• The timeframe in the Explore query must be an equal or smaller timeframe than what is specified in theperiod parameter of the PoP measure. For example, if the PoP measure'sbased_on_time is defined with a timeframe from theorders.created dimension group and the PoP measure is defined withperiod: month, the Explore query must include a timeframe from theorders.created dimension group that is equal to or smaller than a month, such asorders.created_date. The timeframe in the Explore query must be smaller because, for example, you can't do a month-over-month comparison of a year timeframe.
• The timeframe in the Explore query must itself contain timestamp information. For example, theyear,month, anddate timeframes of a dimension group provide actual timestamp information. In contrast, theday_of_week timeframe is abstracted from the underlying timestamp to provide a value such asWednesday. Similarly, timeframes such asmonth_name,month_num, andday_of_month don't provide timestamp information themselves, so they can't be used by PoP measures to calculate values for the previous period. However, if you include in the Explore query a timestamp such asdate, that will provide the PoP measure with timestamp information that it can use to calculate values for the previous period. You canalso include theday_of_week timeframe in the Explore query, because the PoP measure can use thedate timeframe information for calculations.

As long as you meet these requirements in your Explore query, you can add other fields and timeframe dimensions in the Explore query, but all of the timeframes in the Explore query must be equal to or smaller than the timeframe from the PoP measure'speriod timeframe. When you run an Explore query with a PoP measure, Looker automatically applies the minimum timeframe granularity from the query to the timeframe used by the PoP measure. In theexample Explore shown at the beginning of this page, the PoP measures have all been defined in LookML withperiod: year. This means that for whatever timeframe is selected in the Explore query -- in this case, a monthly timeframe -- the PoP measure will return the results for the same timeframe in the previous year.

If you want to see which timeframes are supported with your PoP measure in an Explore, you can test different timeframes without having to run queries. Click theSQL tab of the Explore'sData section, and then add fields and filters from the Explore's field picker. If the PoP measure can't calculate your the query with your selected fields and filters, theSQL tab will show a message that the SQL can't be generated.

If you do run a query where the SQL can't be generated, the Explore window will return an error with the details and a link to the relevant LookML.

Examples

The following sections show some examples of different PoP measures and Explore queries:

• Comparing counts to year-over-year and month-over-month PoP measures
• Howvalue_to_date affects PoP measure values

Comparing counts to year-over-year and month-over-month PoP measures

Here is the LookML for an exampletotal_births measure, abirth dimension group oftype:time, and two PoP measures that are based on thetotal_births measure and that use thebirth dimension group as theirbased_on_time field:

  dimension_group: birth {    type: time    timeframes: [raw, time, date, week, month, quarter, year]    sql: ${TABLE}.birth_date ;;  }    measure: total_births {    type: sum    sql: ${TABLE}.total_births ;;  }  measure: total_births_last_year {    type: period_over_period    kind: previous    based_on: total_births    based_on_time: birth_year    period: year    value_to_date: no    value_format_name: decimal_0  }  measure: total_births_last_month {    type: period_over_period    kind: previous    based_on: total_births    based_on_time: birth_year    period: month    value_to_date: no    value_format_name: decimal_0  }

Note the following about these fields:

• Both of the PoP measures are defined withkind: previous, so they both provide the value of the measure from the previous period.
• Both of the PoP measures are defined withvalue_to_date: no, so they both calculate the value of the measure for the entire timeframe (that is, the minimum timeframe granularity from the query).
• Both of the PoP measures are defined withbased_on_time: birth_year, so they both use the underlying timestamp of thebirth dimension group.
• Thetotal_births_last_year PoP measure is defined withperiod: year, and thetotal_births_last_month PoP measure is defined withperiod: month.

Here is an Explore query that includes all three of the measures and thebirth_month dimension timeframe:

Note the following about the Explore results:

• The smallest dimension timeframe in the Explore query isbirth_month, so the PoP measure provides monthly values.
• In the row for the most recent month,2024-07, theTotal Births Last Month value shows the total births for the previous month, 2024-06. You can verify this by looking at theTotal Births value for the2024-06 row. The two values match.
• In the row for the most recent month,2024-07, theTotal Births Last Year value shows the total births for the same month (07) in the previous year (2023). You can verify this by looking at theTotal Births value for the2023-07 row. The two values match.

Howvalue_to_date affects PoP measure values

Similar to the previousexample, here is the LookML for thetotal_births measure and thebirth dimension group oftype:time and two PoP measures that are based on thetotal_births measure and that use thebirth dimension group as theirbased_on_time field. However, in this example, thetotal_births_last_year_value_to_date PoP measure is defined withvalue_to_date: yes and thetotal_births_last_year PoP measure is defined withvalue_to_date: no:

  dimension_group: birth {    type: time    timeframes: [raw, time, date, week, month, quarter, year]    sql: ${TABLE}.birth_date ;;  }    measure: total_births {    type: sum    sql: ${TABLE}.total_births ;;  }  measure: total_births_last_year {    type: period_over_period    kind: previous    based_on: total_births    based_on_time: birth_year    period: year    value_to_date: no    value_format_name: decimal_0  }  measure: total_births_last_year_value_to_date {    type: period_over_period    kind: previous    based_on: total_births    based_on_time: birth_year    value_to_date: yes    period: year    value_format_name: decimal_0  }

Here is an Explore query that includes all three of the measures and thebirth_year dimension timeframe. This Explore query was run on June 4 at 16:25:08, which is significant for thevalue_to_date: yes PoP measure.

The Explore results show how thevalue_to_date subparameter changes the calculation for the PoP measures:

Note the following about the Explore results:

• In the row for the most recent year,2024, theTotal Births Last Year value shows the total births for the previous year, 2023. You can verify the calculation by looking at theTotal Births value for the2023 row. The two values match.
• In the row for the most recent year,2024, theTotal Births Last Year Value to Date value is less than theTotal Births Last Year value. This is because the Explore query was run on June 4 at 16:25:08, and because thetotal_births_last_year_value_to_date PoP measure is defined withvalue_to_date: yes, so Looker calculated the yearly values using only the data up to June 4 at 16:25:08 for each year.

Filtering Explore queries that include PoP measures

Note the following for filtering Explore queries that include PoP measures:

• Filtering is supported for Explore queries that include PoP measures. However, you can't filter on a PoP measure itself. For example, in thefirst example Explore that queries on thebirth_month dimension and thetotal_births,total_births_last_year, andtotal_births_last_month PoP measures, you couldn't filter that query on thetotal_births,total_births_last_year, ortotal_births_last_month PoP measures.
• When you filter on a field that's associated with thebased_on_time parameter of a PoP measure, if the timeframe of the filter is finer than the timeframe of the query, the PoP measure will show only the results for the filter-value portion of the query's timeframe. For example, if you query on theorders.created_year dimension and you filter the query for the month of January, for each year the PoP measure will show the values for January only. This can be mistaken for being the results for the full year.
• For PoP measure Explore queries, to calculate data for the PoP measure, Looker retrieves data for an extra time period at the least granular time period granularity of the query. For example, if you create an Explore query with a monthly dimension, a PoP measure defined withperiod: year, and a filter for the last 6 months, Looker will identify the least granular granularity in the query, which in this example would be the PoP measure'syear time period. In this example, Looker would retrieve the last 6 months of data plus an additional year's worth of data so that it can compare each of the last 6 months to the same month in the previous year.
• As described inRequirements for Explore queries with PoP measures, Explore queries that include PoP measures must have a time dimension that is appropriate for theperiod that is associated with the PoP measure. If you don't select a time dimension from the Explore's field picker, Looker can derive the required information from time dimensions in the Explore's filters. In this case, Looker will sort the Explore query results by the filter's time dimension.

Visualizations with PoP measures

Thetable chart visualization is recommended for PoP measures. Other visualization options may work as well, depending on the fields in your Explore query.

If you use a visualization other than a table chart, verify that your visualization is clear. Because PoP measures provide comparisons to a previous time period, visualizations with PoP measures may be misleading. For example, a year-over-year PoP measure that's defined askind: previous will show last year's value for this year's date. If your Explore query includes the current year's value along with the year-over-year PoP measure, the current year will have two values in the visualization.

If you use a visualization other than a table chart, verify that your visualization clearly indicates that any PoP measures are a comparison to a previous time period.

Limitations for PoP measures

Note the following limitations of PoP measures:

• PoP measures are supported only for LookML projects that use thenew LookML runtime. If theUse Legacy LookML Runtime legacy feature is enabled on your instance, themanifest file for your project must include anew_lookml_runtime:yes statement.
• PoP measures aren't supported with theLooker connector in Looker Studio.
• PoP measures must be based on an aggregate measure, as described in thebased_on section. You cannot base a PoP measure on a non-aggregate measure.
• For BigQuery connections on instances where theBI Engine Symmetric Aggregates Labs feature is enabled, PoP measures are supported, but SQL queries with PoP measures won't use the BI Engine Symmetric Aggregates feature.
• PoP measures don't support cohort analysis.
• PoP measures don't support rolling calculations.
• PoP measures always compare the current period to the previous period. You can't configure a PoP measure to compare the current period to a different period other than the previous period. For example, you cannot create a PoP measure to compare May of last year to December of this year.
• PoP measures are not supported with custom calendars, such as retail 4-5-4 calendars. See theperiod section for the time periods that PoP measures support.
• PoP measures are not supported with custom periods, such as the current two weeks compared to the previous two weeks.

• Liquid parameters are not supported in the parameters of a PoP measure. However, if thebased_on orbased_on_time fields of a PoP measure point to a dimension that is defined with Liquid, that Liquid will be processed.

• PoP measures are not supported with the following Looker features:

  • Aggregate awareness
  • Subtotals

• PoP measures can't be used to create acustom field.

• You can't select the week timeframe in an Explore query with a PoP measures unless the PoP measure is defined withperiod: week orperiod: date.

• PoP measures with periods that are defined with fiscal timeframes can't be used in Explore queries with non-fiscal timeframes. Also, PoP measures with periods that are defined with non-fiscal timeframes can't be used in queries with fiscal timeframe dimensions.

• PoP measures support fiscal month offset, in that the PoP measure'sbased_on_time parameter will inherit thefiscal_month_offset value from the LookML model file that is associated with the Explore. If you define a PoP measure withfiscal_year orfiscal_quarter, the PoP measure will be supported in an Explore query only if the Explore query specifies afiscal_year orfiscal_quarter timeframe. In that case, thefiscal_offset_month is honored.

• Theperiod of the PoP measure must be equal to or larger than the timeframe that's selected in the Explore query. For example, for a PoP measure that's defined withperiod: month, the Explore query must have a timeframe dimension of a month or smaller, such as week or day.

Supported database dialects for PoP measures

The following table shows which dialects support PoP measures in the latest release of Looker: