Tracking change over time—month-over-month, year-over-year—is essential for monitoring performance. Until recently, doing this in Looker meant relying on table calculations, which could be:
- Hard to read and maintain
- Prone to human error
- Limited to visualization-only logic
Looker's new period-over-period measures help bring time-based comparisons into LookML itself - making it easier to build reliable, reusable metrics. (https://cloud.google.com/looker/docs/period-over-period)
🧱 Before: Table Calculations
Previously, getting last year’s value for a metric required a workaround like the following table calculation:
offset(${payment_transactions_fact.count_successful_transactions}, 12)
Limitations we encountered with this approach:
- ⚠️ Only works in visualizations—can’t be filtered or reused elsewhere
- 🧩 Logic cannot get duplicated across dashboards
- 🔍 Harder to QA and understand over time
- 📉 Fragile if the time grain or sort order changes
✅ Now: LookML Period-over-Period Measures
With the period_over_period measure type, we can now move this logic into our LookML layer.
Example: Last Year’s Value
measure: count_successful_transactions_last_year {
type: period_over_period
description: "Successful transactions from the previous year"
based_on: count_successful_transactions
based_on_time: created_year
period: year
kind: previous
}
Example: Year-over-Year % Change
measure: count_successful_transactions_last_year_relative_change {
type: period_over_period
description: "Year-over-year % change in successful transactions"
based_on: count_successful_transactions
based_on_time: created_year
period: year
kind: relative_change
value_format_name: percent_0
Example: Looker Explore
Example: Looker Visualisation
🏢 How We Use It
At Super, we’ve started using these measures to track key metrics like:
- 📦 Sales – Year-over-year trends
- 🧲 Customer and merchant retention – Month-over-month comparisons
- 🚀 Feature impact – Week-over-week shifts after a product launch
Because these are built into our LookML layer, they can be reused across different dashboards, which helps ensure consistency and saves time.
🔍 Why This Helps
- ✅ Cleaner and centralized logic – Easier to review and test
- 📊 Works across dashboards – Filterable, sortable, and visualization-friendly
- 🔧 Adaptable – Supports different time periods (week, month, quarter, year)
💡 Final Thoughts
This feature might seem small, but it's been a noticeable improvement for our data team. It reduces repetitive work, helps standardise time-based comparisons, and makes dashboards easier to maintain and trust.
Top comments (0)