> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://help.sigmacomputing.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://help.sigmacomputing.com/_mcp/server.

# DateLookback

The **DateLookback** function returns the value of a variable at a previous point in time (or lookback period) determined by a specified date and offset.

**DateLookback** is a [date function](/docs/date-functions).

The **DateLookback** function is best used on a grouped column with a [truncated date](/docs/datetrunc) and unique values. If the grouping includes additional non-date columns, or if the grouping includes additional logic such as a fiscal year calculation, consider using the **Lookup** function instead. For an example using the **Lookup** function for this use case, see <a href="https://community.sigmacomputing.com/t/why-doesn-t-datelookback-work-when-i-m-using-custom-fiscal-years-quarters/3845" target="_blank">Why doesn’t DateLookback work when I’m using custom fiscal years/quarters?</a> in the Sigma Community.

## Syntax

```
DateLookback(value, date, amount, period)
```

Function arguments:

|            |            |                                                                                                                                                              |
| :--------- | :--------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **value**  | (required) | The value to look up. Can be a column to reference, a formula to compute, or a constant.                                                                     |
| **date**   | (required) | The date to reference when offsetting the lookback period. Can be a date, date column, or formula that returns a date.                                       |
| **amount** | (required) | The number of periods to offset the lookback period. Can be a positive integer (for lookback) or negative integer (for lookahead).                           |
| **period** | (required) | The unit of time to use for the offset and lookback period. Can be `"year"`, `"quarter"`, `"month"`, `"week"`, `"day"`, `"hour"`, `"minute"`, or `"second"`. |

## Notes

* The **period** argument cannot be a column.
* The **period** argument does not need to match the truncation of the grouping in the **date** column. For example, a table grouped by day can use a week period to look back a week from each day.
* If the **period** argument is a smaller unit of time than the truncation of the **date** column, the function returns `Null`. For example, a table grouped by day cannot use an hour period to look back an hour from each day.
* If there is no data for the lookback period, or the lookback period is outside the range of the data, the function returns `Null`. For example, if the data is grouped by the hour, and the lookback period is 1 day, the function returns `Null` for any rows in the first day, as there is no data for that hour on the previous day.
* The **value** must be unique in the grouping of the **date** column. If there are multiple values for the same date or truncated date, the function returns `Null`.
  * For example, if a table is grouped by *Quarter of Date* and *Status* in the same grouping, a **DateLookback** that targets the *Status* returns `Null` any time there are multiple *Status* values in the underlying grouping. Instead, group by date only, and move *Status* to a separate grouping or calculation.

## Examples

A table includes an *Annual Gross Profit* column containing the gross profit for each year between 2019 and 2023. You can use the **DateLookback** function to return the previous year’s gross profit and facilitate a period-over-period analysis.

```
DateLookback([Annual Gross Profit], [Year], 1, "year")
```

The formula determines a one-year offset from the period in the *Year* column, then references the offset period and returns the corresponding value from the *Annual Gross Profit* column.

![A table with annual gross profit and lookback (1 year) shows the previous year's gross profit for each year in the lookback column](https://sigma-docs-screenshots.s3.us-west-2.amazonaws.com/Functions/datelookback_example.png)

```
DateLookback([Monthly Profit], [Month of Date], 2, "month")
```

The formula determines a two-month offset from the period in the *Month of Date* column, then references the offset period and returns the corresponding value from the *Monthly Profit* column.

![A table with groupings by year and month shows monthly profit. The Monthly profit (2 months ago) column shows the profit two months ago for each month, corresponding to the Monthly profit column](https://files.buildwithfern.com/sigma.docs.buildwithfern.com/53a0dff59b623b8c72426e679b0ba2b5c99c1c66fe248f7b76ccebce721c9033/assets/docs-images/f763d8b2232cb3fb68b6c8c870d86c07187bf188152630b2e38aa081df36712b-new_datelookback_ex.png)

A table tracks the monthly *Status* of each account, grouped by *Month of Date*. You can use the **DateLookback** function to compare each account's current status to its status one month earlier.

```
DateLookback([Status], [Month of Date], 1, "month")
```

The formula determines a one-month offset from the period in the *Month of Date* column, then returns the corresponding *Status* value from that earlier month. However, if the **value** for the targeted **date** grouping contains multiple values - for example, a month where the status changes from one week to the next - the function returns `Multiple values` rather than the most recent earlier status.

## Related resources

* <a href="https://www.youtube.com/watch?v=-tx0f_HZGbE" target="_blank" rel="noopener noreferrer">
    Video How To: DateLookback() and Filters
  </a>