How to do period-over-period analysis in Lightdash

Learn how to do period-over-period analysis in Lightdash with examples and best practices.

Period comparison (beta)

This feature is currently in beta.

We’re actively testing and improving it.

We’ve made significant improvements to how you generate period-over-period comparisons. You can now customize a PoP metric’s series type, label, show value labels, and more.

To add a period comparison:

1. Select a time/date dimension
2. Select a metric
3. On the results table, click on a metric’s column and select Add period comparison
4. Choose the time dimension and offset, then hit save
5. Add the generated metric to your chart

You can also ask an AI agent for comparisons in natural language — for example, “compare revenue to last month” or “year-over-year orders by week”.

---

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.

Table calculation for week of year descriptions

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)

3. Create a percent change calculation:

(${event_count} - ${previous_year_value}) / ${previous_year_value}

4. 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

   Date range period parameter syntax

   dbt v1.9 and earlier

   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"
   

   dbt v1.10+

   models:
     - name: dbt_orders
       description: 'This table contains information on all the confirmed orders and their status'
       config:
         meta:
           parameters:
             date_range:
               label: "Date Range"
               description: "Choose a date range"
               options:
                 - "yesterday"
                 - "last 7 days"
                 - "last 30 days"
               default: "last 7 days"
   

   Lightdash YAML

   type: model
   name: dbt_orders
   
   description: 'This table contains information on all the confirmed orders and their status'
   
   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.

   Additional dimension syntax

   dbt v1.9 and earlier

   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
   

   dbt v1.10+

   models:
    - name: dbt_orders
      description: 'This table contains information on all the confirmed orders and their status'
      config:
        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.'
          config:
            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
   

   Lightdash YAML

   type: model
   name: dbt_orders
   
   description: 'This table contains information on all the confirmed orders and their status'
   
   label: Orders
   
   parameters:
     date_range:
       label: "Date Range"
       description: "Choose a date range"
       options:
         - "yesterday"
         - "last 7 days"
         - "last 30 days"
       default: "last 7 days"
   
   dimensions:
     - name: order_date
       description: 'Timestamp of order placement by user.'
       type: timestamp
       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' ]
       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.

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:

Custom date range parameter configuration

dbt v1.9 and earlier

models:
  - name: dbt_orders
    meta:
      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

dbt v1.10+

models:
  - name: dbt_orders
    config:
      meta:
        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

Lightdash YAML

type: model
name: dbt_orders

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

Custom date range additional dimension code

dbt v1.9 and earlier

columns:
  - 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

dbt v1.10+

columns:
  - name: order_date
    description: 'Date of order placement by user.'
    config:
      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

Lightdash YAML

dimensions:
  - name: order_date
    description: 'Date of order placement by user.'
    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: