1. Calculation functions
  2. All Functions
  3. Time and Date Functions
  4. MOVINGSUM

Aggregates values between start and end periods that are relative to each time period in a module.

This function can be useful to create rolling forecasts or moving averages.

Syntax

MOVINGSUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])

ArgumentData typeDescription
Line item to aggregate (required)Number, Boolean, date, time period, list, or textThe line item to aggregate over a period of time.
Start period (optional)NumberThe period to start the aggregation from, relative to each time period. If omitted, the function aggregates all periods.
End period (optional)NumberThe period to end the aggregation at, relative to each time period. If omitted, the function only returns the value for period in the Start period argument.
Aggregation method (optional)KeywordThe aggregation method to use. If omitted, the function uses the SUM Aggregation method.

Returns

The MOVINGSUM function returns a result of the same format as the Line item to aggregate argument.

Arguments

Line item to aggregate

The value to aggregate over a period of time, relative to each period in a module. The Line item to aggregate argument can be a line item of any format.

Start period

The Start period argument selects the time period to start aggregation from, relative to each time period the function applies to. This argument must be a number. A negative number selects a period before each period in a module, and a positive number a period after each period in a module. Zero selects each period that the function applies to.

End period

The End period argument  selects the time period to end aggregation at, relative to each time period the function applies to. This argument must be a number. A negative number selects a period before each period in a module, and a positive number a period after each period in a module. Zero selects each period that the function applies to.

Aggregation method

The keywords for the Aggregation method argument are:

  • SUM, which returns the sum.
  • AVERAGE, which returns the mean average.
  • MIN, which returns the lowest value.
  • MAX, which returns the highest value.
  • ANY, which returns a value of TRUE if any values of a Boolean-formatted item are TRUE.
  • ALL, which returns a value of TRUE if all values of a Boolean-formatted item are TRUE.
  • FIRSTNONBLANK, which returns the first non-blank value.
  • LASTNONBLANK, which returns the last non-blank value.
  • TEXTLIST, which concatenates the values of a text-formatted line item.

Constraints

The MOVINGSUM function has these constraints:

  • You can only provide a latter argument if you provide all prior arguments.
  • The result line item must be the same format as the Line item to aggregate argument.
  • If you use a decimal number for the Start period or End period arguments, it rounds to the nearest integer.
  • If you use an expression for the Line item to aggregate argument that contains another function, that function must use the same Time Range as the result line item.
  • If the Start period or End period arguments specify periods that are outside of the module's Time Range, they're omitted from the aggregation.

Excel equivalent

The MOVINGSUM function has no Excel equivalent.

Examples

In these examples, Revenue is a number-formatted line item that contains values for each month. The module uses months for its timescale and the current period selected in the Model Calendar is June 2020.

Example with only Line item to aggregate argument

MOVINGSUM(Revenue)

This example only contains the mandatory Line item to aggregate argument. If you only use the Line item to aggregate argument, the MOVINGSUM function sums all values for the line item within the module (excluding the Brought-Forward period, if the module contains one).

Start period and End period examples

If you provide the Start period argument without the End period argument, the MOVINGSUM function returns the value from the Start period.

The three formulas below use a number for the Start period argument. A negative number selects a period before the current period, and a positive number a period after the current period. Zero selects the current period. The results that the formula returns remain the same as the current period updates, as the argument specifies a period relative to each time period in the module.

FormulaDescription
MOVINGSUM(Revenue, 0)Returns the value from the same time period for the Revenue line item.
MOVINGSUM(Revenue, -1)

Returns the value from the previous time period of the Revenue line item.

You can also use the LAG or OFFSET functions to achieve this result.

MOVINGSUM(Revenue, 1)

Returns the value from the next time period of the Revenue line item.

You can also use the LEAD or OFFSET functions to achieve this result.

The formulas below contain both the Start period and End period arguments. As the formulas do not contain the Aggregation method argument, they use the default aggregation method, SUM.

FormulaDescription
MOVINGSUM(Revenue, -11, 0)Returns the sum of the values of the Revenue line item from 11 periods earlier to the period the function is used in.
MOVINGSUM(Revenue, 0, 11)Returns the sum of the values of the Revenue line item from the period the function is used in until 11 periods later.
MOVINGSUM(Revenue, 0, 2)Returns the sum of the values of the Revenue line item from the period the function is used in until two periods later.

Values from outside of the module's Time Range are not included in aggregations, so ensure your module contains all of the periods you want to aggregate.

Aggregation method examples

These examples show the results of the different keywords for the Aggregation method argument.

A module named MOVINGSUM Examples. It contains example formulas which demonstrate the effect of each keyword for the Aggregation method keyword.

The 12 months of 2020 display on columns and the module uses Months for its timescale. These 12 months are the Time Range for the module, so formulas exclude values outside of these 12 months from aggregations.

A number of line items display on rows. These contain the line items the formulas use for the Line item to aggregate argument: NumberBooleanDate, and Text. They’re each of the same format as their name suggests. The other line items contain the example formulas in the table below.

The results column in the table below refers to the values in the June 2020 period in the module.

FormulaResult for June 2020Description
MOVINGSUM(Number, -2, 0, SUM)1,200

This formula aggregates the number-formatted values between the Start period of April 2020 and End period of June 2020.

The formula uses the SUM keyword, so it sums the three periods' values of 300, 200, and 700.

MOVINGSUM(Number, -2, 0, AVERAGE)400

This formula aggregates the number-formatted values between the Start period of April 2020 and End period of June 2020.

The formula uses the AVERAGE keyword, so it calculates and returns the mean average of the three periods' values of 300, 200, and 700.

MOVINGSUM(Number, -2, 0, MIN)200

This formula aggregates the number-formatted values between the Start period of April 2020 and End period of June 2020.

The formula uses the MIN keyword, so it returns the smallest of the three periods' values of 300, 200, and 700.

MOVINGSUM(Number, -2, 0, MAX)700

This formula aggregates the number-formatted values between the Start period of April 2020 and End period of June 2020.

The formula uses the MAX keyword, so it returns the largest of the three periods' values of 300, 200, and 700.

MOVINGSUM(Boolean, -2, 0, ANY)TRUE

This formula checks the Boolean values between the Start period of April 2020 and End period of June 2020.

The formula uses the ANY keyword, so it checks if any of the Boolean values in this period are TRUE. If any are true, the formula returns a value of TRUE.

MOVINGSUM(Boolean, -2, 0, ALL)FALSE

This formula checks the Boolean values between the Start period of April 2020 and End period of June 2020.

The formula uses the ALL keyword, so it checks if all of the Boolean values in this period are TRUE. If all are true, the formula returns a value of TRUE.

MOVINGSUM(Date, 0, 2, FIRSTNONBLANK)2/1/2020

This formula checks the date-formatted values between the Start period of June 2020 and End period of August 2020.

The formula uses the FIRSTNONBLANK keyword, so it returns the first non-blank value between the Start period and End period.

MOVINGSUM(Date, -5, 0, LASTNONBLANK)2/1/2020

This formula checks the date-formatted values between the Start period of January 2020 and End period of June 2020.

The formula uses the LASTNONBLANK keyword, so it returns the last non-blank value between the Start period and End period.

MOVINGSUM(Text, 0, 2, TEXTLIST)f, g, h

This formula aggregates the text-formatted values between the Start period of June 2020 and End period of August 2020.

The formula uses the TEXTLIST keyword, so it returns all of the text-formatted values between the Start period and End period. The formula inserts a comma and a space between each returned value.

Similar functions: