Skip to main content
Period-over-period analysis is one of the most common ways to evaluate how metrics change over time. Whether you’re looking at month-over-month growth, year-over-year comparisons, or week-over-week performance, Lightdash makes it easy to build these analyses directly in your dashboards. This guide walks through several examples of how to do period-over-period analysis in Lightdash. You can also watch the video walkthrough if you’d prefer a visual demo.

​
Big Value Charts: Simple Comparisons

The fastest way to get started is with the Big Value chart type.

​
Month-over-month

  • Pull two months of data.
  • Use the Big Value chart and select Compare to previous row
  • Lightdash automatically shows the comparison.
That’s it!

​
Same month, previous Year

  • Pull ~13 months of data with MONTH and MONTH_NUM time dimensions and your metric.
  • Create a table calculation to filter only the current month and the same month last year:
(extract(month from current_date) - 1) = ${tracks.timestamp_month_num}
This table calculation relies on the MONTH_NUM time interval.
  • Filter where the value of this table calculation is true.
  • Then use the Big Value chart with Compare to previous row

​
Trend line comparisons

Sometimes you need more than a single value—you want to see how trends evolve.

​
By week of year

  • Add week of year time dimension (1-52) on the X-axis.
  • Plot the metric (e.g. event_count).
  • Group by year.
  • This allows you to compare trends across years.
Check minute 1:45 in the Loom video for an example.
Bonus: if you want a more business-user-friendly version you can use the table calculation below to add descriptions for each week number to get a chart like this.
case ${dbt_orders.order_date_week_num}
  when 0  then '0: late Dec/early Jan'
  when 1  then '1: early Jan'
  when 2  then '2: early/mid Jan'
  when 3  then '3: mid Jan'
  when 4  then '4: late Jan/early Feb'

  when 5  then '5: early Feb'
  when 6  then '6: early/mid Feb'
  when 7  then '7: mid/late Feb'
  when 8  then '8: late Feb/early Mar'

  when 9  then '9: early Mar'
  when 10 then '10: early/mid Mar'
  when 11 then '11: mid Mar'
  when 12 then '12: mid/late Mar'
  when 13 then '13: late Mar/early Apr'

  when 14 then '14: early Apr'
  when 15 then '15: early/mid Apr'
  when 16 then '16: mid Apr'
  when 17 then '17: late Apr/early May'

  when 18 then '18: early May'
  when 19 then '19: early/mid May'
  when 20 then '20: mid May'
  when 21 then '21: mid/late May'
  when 22 then '22: late May/early Jun'

  when 23 then '23: early Jun'
  when 24 then '24: early/mid Jun'
  when 25 then '25: mid Jun'
  when 26 then '26: late Jun/early Jul'

  when 27 then '27: early Jul'
  when 28 then '28: early/mid Jul'
  when 29 then '29: mid Jul'
  when 30 then '30: late Jul/early Aug'

  when 31 then '31: early Aug'
  when 32 then '32: early/mid Aug'
  when 33 then '33: mid Aug'
  when 34 then '34: mid/late Aug'
  when 35 then '35: late Aug/early Sep'

  when 36 then '36: early Sep'
  when 37 then '37: early/mid Sep'
  when 38 then '38: mid Sep'
  when 39 then '39: late Sep/early Oct'

  when 40 then '40: early Oct'
  when 41 then '41: early/mid Oct'
  when 42 then '42: mid Oct'
  when 43 then '43: late Oct/early Nov'

  when 44 then '44: early Nov'
  when 45 then '45: early/mid Nov'
  when 46 then '46: mid Nov'
  when 47 then '47: mid/late Nov'
  when 48 then '48: late Nov/early Dec'

  when 49 then '49: early Dec'
  when 50 then '50: early/mid Dec'
  when 51 then '51: mid Dec'
  when 52 then '52: late Dec'
  when 53 then '53: late Dec/early Jan'
end

​
By day of month with running totals

  • Compare June vs July (or any two months).
  • Use day of month time dimension (1-31) on the X-axis, grouped by month.
  • Add a running total table calculation.
  • Optionally, use bars in the background to show cumulative progress.
Check minute 2:30 in the Loom video for an example.

​
Compare arbitrary weeks

You can also compare arbitrary weeks side by side.
  • Select specific weeks (e.g. 2, 8, 12).
  • Use week of year as the grouping dimension.
  • Apply a running total to see how each week accumulates.
  • This shows how different weeks trend across the same range.
Check minute 3:00 in the Loom video for an example.

​
Year-over-year by month

For a full year-over-year comparison across months:
  1. Pull two years worth of monthly of data.
  2. Use the lag() function in a table calculation to find the value from the same month last year:
lag(${event_count}, 12)
  1. Create a percent change calculation:
(${event_count} - ${previous_year_value}) / ${previous_year_value}
  1. Filter out rows where previous_year_value is null.
Check minute 3:45 in the Loom video for an example.
⌘I