Skip to main content
Specifies a filter dynamically relative to the date filter applied to a workbook query. This is an advanced filter primarily used in filtered measures for period-over-period comparisons. Using the period-over-period (PoP) workbook functionality will create this measure type for you. After enabling PoP in the workbook, flatten the pivot and then add the fields produced by the flattening to the workbook.

​
Requirements

Must be used with cancel_query_filter: true. Without cancel_query_filter the measure will filter for both the query date and the offset date, which will typically return zero results.

​
Syntax

measures:
  <measure_name>:
    aggregate_type: <type>
    filters:
      <time_field>:
        date_offset_from_query: <time_offset>
        cancel_query_filter: true

​
Properties

​
time_field
string
required
The time or date field to filter on, specified in the format view_name.field_name.
​
date_offset_from_query
string
required
The time offset to apply relative to the query’s date filter. For example, 2 years, 30 days, etc.
​
cancel_query_filter
boolean
required
Must be set to true when using date_offset_from_query. This prevents the filter from applying both the query date and the offset date and yielding unexpected (typically zero) results.

​
Examples

For this example, assume the current month is November 2024. If you apply a filter of last month and add the following measure to your query, the results will show the count from October 2022. 
measures:
  count_signups_same_time_two_years_previously:
    aggregate_type: count
    filters:
      created_at:
        date_offset_from_query: 2 years
        cancel_query_filter: true