Functions for period intelligence

These functions perform specific operations on financial periods, and return the result. You can use these to select, process, and calculate financial periods in imports, reports, or models.

ADDPERIOD

Adds the given number of periods to a given period. If the number is negative, it will be substracted from the given period.

ADDPERIOD(data, number)

Example:
data = "2019-01"
ADDPERIOD(data, 2) = "2019-03"

GETPERIOD

Returns the period selected by the user in response to a SELECTPERIOD function, or a period derived from that user selection.

GETPERIOD(type)

Type:
"PER"  - this period (default)
"PREV" - previous period
"NEXT" - next period

"LY.PER"  - this period last year
"LY.PREV" - previous period last year
"LY.NEXT" - next period last year

Example:
GETPERIOD() returns the period selected by the user.

PERIOD

Converts the given date to a period name (YYYY-MM) using calendar months.

In most cases, XLReporting automatically recognizes the used date format and converts text strings to dates. In some cases, if the date format is not correctly interpreted, you can set the "type" parameter to specify the date format.

PERIOD(data, type)

Type:
"DMY" - day-month-year
"MDY" - month-day-year
"YMD" - year-month-day
"MMY" - month as a word (e.g. Feb 2019)

Example:
data = "3-1-2019"
PERIOD(data, "DMY") = 2019-01

PERIOD445

Converts the given date to a period name (YYYY-MM) using a week-based logic.

XLReporting will calculate the period number by comparing the given date to the given start date, using the given type of week calendar. You can optionally pass the start year, if the first period is not the same year as the start date.

PERIOD(data, start, type, year)

Type:
"445" - 4-4-5 periods (default)
"454" - 4-5-4 periods
"544" - 5-4-4 periods

Example:
data = "2019-01-29"
PERIOD445(data, "2019-01-01") = 2019-02
PERIOD445(data, "2018-12-30", "445", "2019") = 2019-02

PERIODNAME

Returns a friendly name for the given period. If you set a fiscal year via the SELECTPERIOD function, the period names will be adjusted accordingly.

PERIODNAME(data, type)

Type:
"Y" - fiscal year (default)
"Q" - fiscal quarter
"P" - fiscal period
"W" - week number
"M" - month name
"N" - period number

Examples:
data = "2016-08"
PERIODNAME(data, "Y") = "2016"
PERIODNAME(data, "Q") = "2016 Q3"
PERIODNAME(data, "M") = "Aug 2016"

PERIODRANGE

Returns an array of period names starting at the given period for the given number of periods. The returned data has the same structure as a cell range or data array, which enables you to pass this data directly into a dropdown editor or other functions that expect a cell range or data array. You can optionally pass an interval (default is 1).

If you provide the optional period parameter, the function will check if that period falls within the range of periods, and return either that period (true) or nothing (false).

You can use the returned value by itself or as a condition inside an IF formula.

PERIODRANGE(data, number, interval, period)

Example:
PERIODRANGE("2019-01", 3) = "2019-01", "2019-02", "2019-03"
PERIODRANGE("2019-01", 3, 12) = "2019-01", "2020-01", "2021-01"
PERIODRANGE("2019-01", 3, 12, "2020-01") = "2020-01"

PERIODS

Calculates the number of periods between the 2 given periods. You can either pass dates, periods (YYYY-MM), or years (YYYY) as parameters.

PERIODS(data, period)

Example:
data = "2019-01"
PERIODS(data, "2019-03") = 2

PERIODSUM

Returns the total sum of values based on the given period type and the selected period.

This function requires that you also use a SELECTPERIOD function in a filter to allow the user to select a period. Both functions work together to identify, filter, and process the appropriate data based on the selected period.

If you use this function in a Report, the "data" parameter references the field for which you want the sum for the given period. If you use this function in a Model, the "data" parameter needs to reference a cell range or data array of multiple rows and 2 columns, of which the 1st column needs to contain the period, and the 2nd column needs to contain the value for which you want the sum for the given period.

If you set a fiscal year via the SELECTPERIOD function, the period sums will be adjusted accordingly.

Optionally, you can also provide a logical condition, in which case amounts are only counted if the condition is met.

PERIODSUM(data, type, condition)

Type:
"PER"  - this period (default)
"PREV" - previous period
"NEXT" - next period
"YTD"  - year-to-date
"FY"   - full year
"OPEN"  - opening balance this year
"MOV"  - movement this period
"MOVP" - movement previous period
"TOGO" - year-to-go

"LY.PER"  - this period last year
"LY.PREV" - previous period last year
"LY.NEXT" - next period last year
"LY.YTD"  - last year to date
"LY.FY"   - full last year
"LY.MOV"  - movement this period last year
"LY.MOV"  - opening balance last year
"LY.MOVP" - movement previous period last year
"LY.TOGO" - last year-to-go

"PTD"    - period-to-date (you must select a full date in SELECTPERIOD)
"LY.PTD" - period-to-date last year (you must select a full date in SELECTPERIOD)
"FR.TO"  - from to period (you must provide a condition, and the from and to period)
"ROLL"   - rolling number of periods, relative to the selected period (you must provide a condition, and the number of periods; that number can be negative for past periods, or positive for future periods, and max 16 months backward or forward)

Examples:
(assuming the selected period is 2019-03)

PERIODSUM(data, "PER") returns the sum for 2019-03
PERIODSUM(data, "YTD") returns the sum for 2019-01 to 2019-03

PERIODSUM(A2:B100, "PER") returns the sum for 2019-03

PERIODSUM(data, "YTD", Unit="100") returns the sum for 2019-01 to 2019-03 only for Unit "100"

PERIODSUM(data, "FR.TO", "", "2019-02", "2019-03") returns the sum for 2019-02 to 2019-03

PERIODSUM(data, "ROLL", "", 3) returns the sum for 2019-03 to 2019-05

PERIODSWITHIN

Calculates the number of periods that the "from" and "to" periods fall within the given period range. You can either pass dates, periods (YYYY-MM), or years (YYYY) as parameters. If you omit either the "from" or "to" parameter, it will be assumed to fall within the given period range.

PERIODSWITHIN(data, period, from, to)

Example:
data = "2019-01"
PERIODSWITHIN(data, "2019-03", "2019-02", "2019-04") = 2

SELECTPERIOD

Prompts the user to select a period from a list of periods.

This function is required, and selection is mandatory, if you want to use the PERIODSUM functions. Both functions work together to identify, filter, and process the appropriate calculations based on the selected period.

Optionally, if you use fiscal years that deviate from calendar years, you can pass the start month (1-12) as an optional parameter. This will automatically adjust the period names and period sums that will be returned by the PERIODNAME and PERIODSUM functions.

SELECTPERIOD(start)

Example:
SELECTPERIOD() returns the period selected by the user.

SELECTPERIOD(3) returns the period selected by the user.
All periods will be processed as fiscal years running from March through February.

SETPERIOD

Sets the given period number within the year of the given period.

Optionally, if you use fiscal years that deviate from calendar years, you can pass the start month (1-12) as an optional parameter. This will automatically adjust the period that will be returned.

SETPERIOD(data, period, start)

Example:
data = "2019-04"
SETPERIOD(data, 12) = "2019-12"
SETPERIOD(data, 12, 7) = "2019-06"