Date.IsInPreviousNYears
DateReturns true if the date falls within the previous N calendar years.
Syntax
Date.IsInPreviousNYears(dateTime as any, years as number) as logicalParameters
| Name | Type | Required | Description |
|---|---|---|---|
dateTime | any | Yes | A date, datetime, or datetimezone value to test. |
years | number | Yes | The number of years back to include. Must be a positive integer. |
Return Value
logical — true if the date falls within the N years before the current year, false otherwise.
Remarks
Date.IsInPreviousNYears returns true if the input date falls within the N complete calendar years preceding the current year. The current year itself is not included; use Date.IsInCurrentYear to also match the current year. The function is re-evaluated on each query refresh.
For example, if the current year is 2026 and years = 3, it matches any date in 2023, 2024, or 2025. This is useful for multi-year historical analysis such as a trailing-3-year revenue trend, a 5-year cohort comparison, or long-term asset depreciation data.
Note that this function counts whole calendar years, not a rolling 365-day window. If you need a rolling lookback, calculate the boundary using Date.AddYears(Date.From(DateTime.LocalNow()), -N) and compare directly.
Examples
Example 1: Filter orders from the previous 3 years
Table.SelectRows(
Sales,
each Date.IsInPreviousNYears([OrderDate], 3)
)OrderID | OrderDate | |
|---|---|---|
| 1 | 1 | 1/15/2024 |
| 2 | 2 | 1/18/2024 |
| 3 | 3 | 2/1/2024 |
| 4 | 4 | 2/10/2024 |
| 5 | 5 | 3/5/2024 |
| 6 | 6 | 3/12/2024 |
| 7 | 7 | 4/1/2024 |
| 8 | 8 | 4/15/2024 |
Example 2: Flag records within the past 2 years
Table.AddColumn(
Sales,
"IsRecentHistory", each Date.IsInPreviousNYears([OrderDate], 2), type logical
)OrderID | OrderDate | IsRecentHistory | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | TRUE |
| 2 | 42 | 3/8/2026 | FALSE |
| 3 | 30 | 5/15/2023 | FALSE |
Example 3: Label rows by historical period for trend analysis
Table.AddColumn(
Sales,
"Period",
each
if Date.IsInCurrentYear([OrderDate]) then "Current Year"
else if Date.IsInPreviousNYears([OrderDate], 3) then "Past 3 Years"
else "Older",
type text
)OrderID | OrderDate | Period | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | Past 3 Years |
| 2 | 42 | 3/8/2026 | Current Year |
| 3 | 30 | 5/15/2022 | Older |