To the given date, this function rolls (adds or subtracts) a number of specific time intervals, returning another date. This function assumes a standard Gregorian calendar.

Syntax

DateRoll ( date, date_part, number )

Parameters

date

A number representing the date. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use this or any of the following functions: Today(), TodateEx(), GetFirstDate(), or GetLastDate(),

Date-time attribute properties of a member can also be used to retrieve this number. For example,

• Product.currentmember.[Intro Date] returns the product introduction date for the current product in context.

• [Cola].[Intro Date] returns the product introduction date for Cola.

date_part

Defined time components as per the standard calendar.

• DP_YEAR - Year of the input date.

• DP_QUARTER - Quarter of the input date.

• DP_MONTH - Month of the input date.

• DP_WEEK - Week of the input date.

• DP_DAY - Day of the input date.

number

Number of time intervals to add or subtract.

Notes

Based on input date_part and dateroll number, the date is moved forward or backward in time.

Example: For input date May 28, 2024 and input dateroll number 5,

• DP_YEAR adds 5 years to the input date (May 28, 2029).

• DP_QUARTER adds 5 quarters (15 months) to the input date (Aug 28, 2025).

• DP_MONTH adds 5 months to the input date (Oct 28, 2024).

  Note that if using an input date near the end of the month and a negative number, results could be different than you might expect. Example for date = May 31, 2025 and number = -1, the resulting date is May 1, 2024, because April only has 30 days.

• DP_WEEK adds 5 weeks to the input date (July 2, 2024).

• DP_DAY adds 5 days to the input date (June 2, 2024).

Example

The following query returns actual weekly sales, rolling back for six months from Apr 2024 (inclusive), for the product Cola in the market California.

SELECT 
 {[Sales]} 
ON COLUMNS,
 {DateToMember
  (
   DateRoll(
     GetFirstDate ([Apr 2024]),
         DP_MONTH,
         6
   ),
   [Time dimension].Dimension, 
   [Time dimension].[WEEKS]
   ): ClosingPeriod([Time dimension].[Weeks], [Apr 2024]))
  } ON ROWS
FROM MySamp.Basic
Where (Actual, California, Cola);