Skip to main content

Relational Planning Functions

Expressions for calculated measures can include any SQL scalar function. In addition, some specific RP functions are defined, as detailed below.

SQL date functions that take a date part as their first argument (DATEADD, DATEDIFF, DATEDIFF_BIG, DATEPART, DATENAME, DATETRUNC, DATE_BUCKET) can be used with any date part SQL Server accepts, including the abbreviations (year/yy, quarter/qq, month/mm, day/dd/d, week/wk and so on). For example, dateadd(d, -1, [In Service Date]) is the day before the in-service date and datediff(month, [Start Date], [End Date]) is the number of month boundaries between two dates.

Several functions below take a window: a start date and an end date. These can be date measures of the item or any date expression built from them, such as eomonth([In Service Date], [Useful Life] - 1).

Dimension members​

An expression can also read the dimension member the value is being calculated for, in any dimension of the model:

ReferenceValue
[Department].[Key]the member's numeric key (an internal number, not the code you see)
[Department].[Name]the member's name
[Department].[Region]any property of the dimension

The brackets can be left out when a name has no spaces: Department.Name. For example, iif([Department].[Region] = 'EMEA', [Salary] * 0.05, 0) gives a 5% uplift to employees of EMEA departments.

ApplyMeritIncrease(base_amount, increase_rate [, divide_periods])​

Applies a merit increase to a base amount, starting on the anniversary date. If increase_rate is between 0 and 1, it is considered a percentage increase, otherwise it is considered a amount increase. The merit increase gets applied cummulatively for each year. Note that the increase is first applied once a full year has passed after the anniversary date. If divide_periods is not provided, it defaults to 1, meaning the increase is applied to the full base amount. otherwise the result is divided by divide_periods.

ApplyTimedIncrease(base_amount, increase_rate, increase_date, [, divide_periods])​

Applies an increase to a base amount, starting on increase_date. If increase_rate is between 0 and 1, it is considered a percentage increase, otherwise it is considered a amount increase. The increase gets applied cummulatively for each year. Unlike ApplyMeritIncrease(), the increase rate gets applied right away starting on increase_date If divide_periods is not provided, it defaults to 1, meaning the increase is applied to the full base amount. otherwise the result is divided by divide_periods.

IsAnniversaryMonth()​

Returns 1 if the current period is in the same calendar month as the anniversary date Note: Will not work with non-calendar periods (e.g. 4-4-5)

IsActive([start_date [, end_date]])​

Returns 1 if the current item is active during the current period (even partially) or 0 otherwise. Must be used like iif(IsActive() = 1, active_value, inactive_value)

Without arguments, the function uses the start/end dates of the item, as set up in the model configuration. With arguments, it tests the current period against the window you provide instead: it returns 1 if the period overlaps the window (even partially) or 0 otherwise. If end_date is omitted or empty, the window has no end; if start_date is empty, it has no beginning. For example, IsActive([In Service Date]) * [Amount] is the amount from the in-service period onwards and 0 before it.

Prorate(value [, start_date [, end_date]])​

Prorates a monthly value based on the overlap between the period start/end dates and the start/end dates of the item

Returns value for a period that is completely inside the window, 0 for a period completely outside it, and value multiplied by the fraction of the period's days that fall inside the window for a period that is only partially covered. Without the date arguments, the window is the start/end dates of the item, as set up in the model configuration (both must be configured, otherwise value is returned unchanged). With start_date and optionally end_date, the window is the one you provide, which lets a model use more than one window. If end_date is omitted or empty, the window has no end; if start_date is empty, it has no beginning. If the window starts on the first day of a period and ends on the last day of a period, every period inside it receives the full value and no period is prorated. For example, straight-line depreciation with a full month in the month the asset goes into service, for exactly [Useful Life] months:

Prorate([Amount] / [Useful Life], datefromparts(year([In Service Date]), month([In Service Date]), 1), eomonth([In Service Date], [Useful Life] - 1))

Note: if value is empty, the result is empty for periods inside the window. Use isnull(value, 0) if an input can be missing, and nullif([Useful Life], 0) to guard a division.

Spread(total, start_date, end_date)​

Spreads total evenly over the periods between start_date and end_date (both dates included). Periods that are completely inside the window receive equal shares. If the window starts or ends in the middle of a period, that period receives a smaller share, in proportion to the number of its days that fall inside the window. The shares always add up to total. Returns 0 if either date is empty or if start_date is after end_date. For example, to spread the cost of an asset over its construction, up to but not including the in-service date:

Spread([Amount], [Project Start], dateadd(d, -1, [In Service Date]))

With a project start of June 1st and an in-service date of October 1st, June to September receive a quarter of the amount each and October receives nothing. With a project start of January 16th and an in-service date of April 1st, January receives 16/31 of a share and February and March a full share each.

ProrateDaysInYear()​

Returns the number of active days in the period divided by total number of days in the year, based on the overlap between the period start/end dates and the start/end dates of the item

ProrateDaysInCalendarYear()​

Returns the number of active days in the period divided by total number of days in the calendar year, based on the overlap between the period start/end dates and the start/end dates of the item

YearlyAccumulatedValue(measure)​

Calculate yearly accumulated value for a calculated measure. Sums up the values of measure for all prior periods in the same year. Note that measure must be a measure, it cannot be an expression.

CalendarYearAccumulatedValue(measure)​

Calculate calendar year accumulated value for a calculated measure. Sums up the values of measure for all prior periods in the same calendar year. Note that measure must be a measure, it cannot be an expression. Note: this function assumes that periods do not span across calendar years

AccumulatedValue(measure)​

Calculate the accumulated value of a calculated measure over the entire life of the item. Sums up the values of measure for all prior periods of the item, regardless of year. Note that measure must be a measure, it cannot be an expression. To include the current period, add the measure itself: AccumulatedValue([Depreciation]) + [Depreciation]. For example, accumulated depreciation as a negative balance is -(AccumulatedValue([Depreciation]) + [Depreciation]). Notes:

  • measure must be defined before the measure that accumulates it, and a measure cannot accumulate itself.
  • The function uses the values already stored for the periods before the ones being calculated. The first time an item is calculated, the calculation must therefore start at (or before) the first period of the item; afterwards, any later range of periods can be recalculated on its own.
  • If the item has no end date, its periods continue to the end of the calculated range, so a balance stays available after the measure it accumulates has dropped to 0.

ApplyCappedRate(measure, cap, rate)​

Calculates measure * rate as long as the accumulated annual value for measure is <= cap Uses a fiscal year cap (as defined in the time dimension). Note that measure must be a measure, it cannot be an expression. It's basically the same thing as writing:

iif(YearlyAccumulatedValue([measure]) >= [cap], 0, [rate] * least([measure], [cap] - YearlyAccumulatedValue([measure])))

ApplyCYCappedRate(measure, cap, rate)​

Like ApplyCappedRate() but uses a calendar year cap. It's basically the same thing as writing:

iif(CalendarYearAccumulatedValue([measure]) >= [cap], 0, [rate] * least([measure], [cap] - CalendarYearAccumulatedValue([measure])))