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.

​
Using parameters for period-over-period analysis

Parameters let you create dynamic period-over-period comparisons in your charts and dashboards. Users can easily toggle between different date ranges to see how metrics are tracking over time — no need to rebuild charts or manually adjust filters. For more on working with parameters, check out our parameters reference guide. This guide walks through an example of how to do period-over-period analysis in Lightdash using parameters, alternatively you can watch our demo tutorial below.
Say you want users to select a time period like yesterday, last 7 days, or last 30 days, and then compare the current period to both the previous period and the same period a year ago. To do this, you:
  1. Set up your date range / period parameter 
    models:
  - name: dbt_orders
    description: 'This table contains information on all the confirmed orders and their status'
    meta:
      parameters:
        date_range:
          label: "Date Range"
          description: "Choose a date range"
          options:
            - "yesterday"
            - "last 7 days"
            - "last 30 days"
          default: "last 7 days"
    You can also take a look at the Date Range parameter setup in our demo dbt project here.
  2. Create a dimension to categorize date periods Next, you need to create an additional dimension inside the dbt_orders model that calculates the period selected in the date_range parameter using conditional case statements. 
    models:
 - name: dbt_orders
   description: 'This table contains information on all the confirmed orders and their status'
   meta:
     parameters:
       date_range:
         label: "Date Range"
         description: "Choose a date range"
         options:
           - "yesterday"
           - "last 7 days"
           - "last 30 days"
         default: "last 7 days"
     label: Orders
   columns:
     - name: order_date
       description: 'Timestamp of order placement by user.'
       meta:
         dimension:
           time_intervals: [
             'HOUR', 'MINUTE_OF_HOUR_NUM', 'HOUR_OF_DAY_NUM',
             'DAY', 'DAY_OF_WEEK_INDEX', 'DAY_OF_MONTH_NUM', 'DAY_OF_YEAR_NUM', 
             'DAY_OF_WEEK_NAME', 'WEEK', 'WEEK_NUM', 
             'MONTH', 'MONTH_NUM', 'MONTH_NAME',
             'QUARTER', 'QUARTER_NUM', 'QUARTER_NAME',
             'YEAR' ]
           type: timestamp
         additional_dimensions:
           order_date_period:
             description: Date period (current period or previous period or previous year or out of range) chosen by the date_range parameter
             type: string
             sql: >
               case
                 -- current period
                 when (
                   (${lightdash.parameters.dbt_orders.date_range} = 'yesterday' and date(${order_date}) = date_sub(current_date(), interval 1 day)) or
                   (${lightdash.parameters.dbt_orders.date_range} = 'last 7 days' and date(${order_date}) between date_sub(current_date(), interval 7 day) and date_sub(current_date(), interval 1 day)) or
                   (${lightdash.parameters.dbt_orders.date_range} = 'last 30 days' and date(${order_date}) between date_sub(current_date(), interval 30 day) and date_sub(current_date(), interval 1 day))
                 )
                 then 'current period'

                 -- previous period
                 when (
                   (${lightdash.parameters.dbt_orders.date_range} = 'yesterday' and date(${order_date}) = date_sub(current_date(), interval 2 day)) or
                   (${lightdash.parameters.dbt_orders.date_range} = 'last 7 days' and date(${order_date}) between date_sub(current_date(), interval 14 day) and date_sub(current_date(), interval 8 day)) or
                   (${lightdash.parameters.dbt_orders.date_range} = 'last 30 days' and date(${order_date}) between date_sub(current_date(), interval 60 day) and date_sub(current_date(), interval 31 day))
                 )
                 then 'previous period'

                 -- previous year
                 when (
                   (${lightdash.parameters.dbt_orders.date_range} = 'yesterday' and date(${order_date}) = date_sub(date_sub(current_date(), interval 1 year), interval 1 day)) or
                   (${lightdash.parameters.dbt_orders.date_range} = 'last 7 days' and date(${order_date}) between date_sub(date_sub(current_date(), interval 1 year), interval 7 day) and date_sub(date_sub(current_date(), interval 1 year), interval 1 day)) or
                   (${lightdash.parameters.dbt_orders.date_range} = 'last 30 days' and date(${order_date}) between date_sub(date_sub(current_date(), interval 1 year), interval 30 day) and date_sub(date_sub(current_date(), interval 1 year), interval 1 day))
                 )
                 then 'previous year'

                 else 'out of range'
               end
    You can also take a look at the order_date_period dimension setup in our demo dbt project here.
  3. Build your chart with the period dimension To create a chart that uses your period parameter, you would need to:
    • select the Order date period additional dimension and the Order count metric
    • specify that order_date_period is not out of range
    • you can optionally filter out other date range groups e.g. you might want to filter out previous year if you want to create a big value chart that compares current period to previous period.
  4. Add your chart to a dashboard You can then add this chart to a dashboard. Users can select an option from the Date Range parameter and the charts will automatically update to display the selected period. Take a look at our example dashboard in our demo site here.

​
Custom date range example

Similar to above, you can also set up a custom date range parameter that allows users to select specific start and end dates for their analysis. Below is the code needed for the parameter and the additional dimension to categorize the date periods for a custom date range: 
parameters:
  custom_range_start:
    label: "Start of custom date range"
    description: "The start of the custom date range"
    type: date
  custom_range_end:
    label: "End of custom date range"
    description: "The end of the custom date range"
    type: date

- name: order_date
  description: 'Date of order placement by user.'
  meta:
    dimension:
      type: date
    additional_dimensions:
      order_date_custom_period:
        description: Date period bucket based on custom Start date and End date parameters
        type: string
        sql: |
          case
            --invalid date range 
            when ${lightdash.parameters.dbt_orders.custom_range_start} is null
              or ${lightdash.parameters.dbt_orders.custom_range_end} is null
              or ${lightdash.parameters.dbt_orders.custom_range_start} > ${lightdash.parameters.dbt_orders.custom_range_end}
              then 'invalid date range'

            --current period 
            when ${order_date_day} >= ${lightdash.parameters.dbt_orders.custom_range_start} 
              and ${order_date_day} <= ${lightdash.parameters.dbt_orders.custom_range_end}
              then 'current period'

            --previous period
            when ${order_date_day} between date_sub(
              ${lightdash.parameters.dbt_orders.custom_range_start},
              interval (date_diff(
                ${lightdash.parameters.dbt_orders.custom_range_end},
                ${lightdash.parameters.dbt_orders.custom_range_start},
                day
              ) + 1) day
            ) and date_sub(
              ${lightdash.parameters.dbt_orders.custom_range_end},
              interval (date_diff(
                ${lightdash.parameters.dbt_orders.custom_range_end},
                ${lightdash.parameters.dbt_orders.custom_range_start},
                day
              ) + 1) day
            )
              then 'previous period'

            --previous year
            when date(${order_date_day}) between date_sub(${lightdash.parameters.dbt_orders.custom_range_start}, interval 1 year)
              and date_sub(${lightdash.parameters.dbt_orders.custom_range_end}, interval 1 year)
              then 'previous year'

            else 'out of range'
          end
The code examples above will give you the following parameter options in the Lightdash UI that will output the custom period labels shown in the chart: