> ## Documentation Index
> Fetch the complete documentation index at: https://lightdash-docs-data-app-visualizations.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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)

<Info>
  **This feature is currently in beta.**

  We're actively testing and improving it.
</Info>

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

<Tip>
  You can also ask an [AI agent](/guides/ai-agents/using-ai-agents#period-over-period-comparisons) for comparisons in natural language — for example, *"compare revenue to last month"* or *"year-over-year orders by week"*.
</Tip>

***

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.

<div
  style={{
position: 'relative',
paddingBottom: '64%',
height: 0,
}}
>
  <iframe
    src="https://www.loom.com/embed/be15f8023f754123a177aa1f928477e6?sid=2a08623c-91be-4cef-9497-251b0d4c9185"
    frameborder="0"
    webkitallowfullscreen
    mozallowfullscreen
    allowfullscreen
    style={{
  position: 'absolute',
  top: 0,
  left: 0,
  width: '100%',
  height: '100%',
}}
  />
</div>

***

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

```sql theme={null}
(extract(month from current_date) - 1) = ${tracks.timestamp_month_num}
```

*This table calculation relies on the `MONTH_NUM` [time interval](/references/dimensions#numeric-options).*

* 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](/references/dimensions#numeric-options) (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.

<Tip>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.</Tip>

<Frame>
  <img src="https://mintcdn.com/lightdash-docs-data-app-visualizations/KTu47iVvTnYI6n68/images/guides/period-over-period/period-over-period-week-of-year.png?fit=max&auto=format&n=KTu47iVvTnYI6n68&q=85&s=81a6565421d31964a03169bc4f204ba3" width="1838" height="944" data-path="images/guides/period-over-period/period-over-period-week-of-year.png" />
</Frame>

<Accordion title="Table calculation for week of year descriptions">
  ```sql theme={null}
  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
  ```
</Accordion>

### By day of month with running totals

* Compare June vs July (or any two months).
* Use **day of month** [time dimension](/references/dimensions#numeric-options) (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:

```sql theme={null}
lag(${event_count}, 12)
```

3. Create a percent change calculation:

```sql theme={null}
(${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](/guides/developer/using-parameters).

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.

<div
  style={{
position: 'relative',
paddingBottom: '51%',
height: 0,
}}
>
  <iframe
    src="https://www.loom.com/embed/048e5b84f44e495dba6a3664e98ff037"
    frameborder="0"
    webkitallowfullscreen
    mozallowfullscreen
    allowfullscreen
    style={{
  position: 'absolute',
  top: 0,
  left: 0,
  width: '100%',
  height: '100%',
}}
  />
</div>

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:

<Steps>
  <Step title="Set up your date range / period parameter">
    #### Date range period parameter syntax

    <Tabs>
      <Tab title="dbt v1.9 and earlier">
        ```yaml theme={null}
        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"
        ```
      </Tab>

      <Tab title="dbt v1.10+">
        ```yaml theme={null}
        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"
        ```
      </Tab>

      <Tab title="Lightdash YAML">
        ```yaml theme={null}
        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"
        ```
      </Tab>
    </Tabs>

    You can also take a look at the `Date Range` parameter setup in our demo dbt project [here](https://github.com/lightdash/lightdash-demo-data-gardening/blob/06ec6e4a87286ec7504d20b10f4b919bd267ebae/dbt-bigquery/models/dbt_orders.yml#L15-L22).
  </Step>

  <Step title="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

    <Tabs>
      <Tab title="dbt v1.9 and earlier">
        ```yaml theme={null}
        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
        ```
      </Tab>

      <Tab title="dbt v1.10+">
        ```yaml theme={null}
        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
        ```
      </Tab>

      <Tab title="Lightdash YAML">
        ```yaml theme={null}
        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
        ```
      </Tab>
    </Tabs>

    You can also take a look at the `order_date_period` dimension setup in our demo dbt project [here](https://github.com/lightdash/lightdash-demo-data-gardening/blob/06ec6e4a87286ec7504d20b10f4b919bd267ebae/dbt-bigquery/models/dbt_orders.yml#L57-L87).
  </Step>

  <Step title="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.

    <Frame>
      <img src="https://mintcdn.com/lightdash-docs-data-app-visualizations/KTu47iVvTnYI6n68/images/guides/period-over-period/period-over-period-example.jpg?fit=max&auto=format&n=KTu47iVvTnYI6n68&q=85&s=d1549db27a26b485e93e77a47a0c2019" width="914" height="806" data-path="images/guides/period-over-period/period-over-period-example.jpg" />
    </Frame>
  </Step>

  <Step title="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.
  </Step>
</Steps>

### 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

<Tabs>
  <Tab title="dbt v1.9 and earlier">
    ```yaml theme={null}
    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
    ```
  </Tab>

  <Tab title="dbt v1.10+">
    ```yaml theme={null}
    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
    ```
  </Tab>

  <Tab title="Lightdash YAML">
    ```yaml theme={null}
    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
    ```
  </Tab>
</Tabs>

#### Custom date range additional dimension code

<Tabs>
  <Tab title="dbt v1.9 and earlier">
    ```yaml theme={null}
    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
    ```
  </Tab>

  <Tab title="dbt v1.10+">
    ```yaml theme={null}
    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
    ```
  </Tab>

  <Tab title="Lightdash YAML">
    ```yaml theme={null}
    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
    ```
  </Tab>
</Tabs>

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:

<Frame>
  <img src="https://mintcdn.com/lightdash-docs-data-app-visualizations/KTu47iVvTnYI6n68/images/guides/period-over-period/custom_range_pop.png?fit=max&auto=format&n=KTu47iVvTnYI6n68&q=85&s=a9a6b537670f07cc5a26651e2f46bb04" width="1990" height="1268" data-path="images/guides/period-over-period/custom_range_pop.png" />
</Frame>