Relative time periods
Use the functions in this section to retrieve data relative to the current day, month, quarter, or year. Plan supports relative time period functions that help you analyze historical and future data by selecting a specified number of preceding or following time periods.
LASTNDAY
The LASTNDAY function returns a range of dates for the specified number of days preceding the current day. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
LASTNDAY(number_of_days)Arguments
number_of_days: The number of preceding days for which data is returned.
Return value
Returns a range of dates for the specified number of preceding days.
Example
LASTNDAY(45)Returns the date range for the 45 days preceding the current day.
LASTNMONTH
The LASTNMONTH function returns a range of dates for the specified number of months preceding the current month. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_months: The number of preceding months for which data is returned.
Return value
Returns a range of dates for the specified number of preceding months.
Example
Returns the date range for the 15 months preceding the current month.
LASTNQTR
The LASTNQTR function returns a range of dates for the specified number of quarters preceding the current quarter. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_quarters: The number of preceding quarters for which data is returned.
Return value
Returns a range of dates for the specified number of preceding quarters.
Example
Returns the date range for the 5 quarters preceding the current quarter. In the following example, it returns the sum of Sales from 2025 Q2 to 2026 Q2, which represents the five quarters preceding the current quarter (2026 Q3).
LASTNYEAR
The LASTNYEAR function returns a range of dates for the specified number of years preceding the current year. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_years: The number of preceding years for which data is returned.
Return value
Returns a range of dates for the specified number of preceding years.
Example
Returns the date range for the 2 years preceding the current year.
NEXTNDAY
The NEXTNDAY function returns a range of dates for the specified number of days following the current day. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_days: The number of following days for which data is returned.
Return value
Returns a range of dates for the specified number of following days.
Example
Returns the date range for the 45 days following the current day.
NEXTNMONTH
The NEXTNMONTH function returns a range of dates for the specified number of months following the current month. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_months: The number of following months for which data is returned.
Return value
Returns a range of dates for the specified number of following months.
Example
Returns the date range for the 4 months following the current month. For example, if the current month is July, it returns the aggregated sales for August, September, October, and November.
NEXTNQTR
The NEXTNQTR function returns a range of dates for the specified number of quarters following the current quarter. Use this function with the SELECT.BYDATE function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_quarters: The number of following quarters for which data is returned.
Return value
Returns a range of dates for the specified number of following quarters.
Example
Returns the date range for the two quarters following the current quarter.
NEXTNYEAR
The NEXTNYEAR function returns a range of dates for the specified number of years following the current year. Use this function with the SELECT function and an aggregate function, such as SUM or AVERAGE, to retrieve and aggregate data over the returned date range.
Syntax
Arguments
number_of_years: The number of following years for which data is returned.
Return value
Returns a range of dates for the specified number of following years.
Example
Returns the date range for the two years following the current year.
Last updated
Was this helpful?