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.
All PERIOD functions are unique to XLReporting, creating functionality that does not exist in Excel.
Adds the given number of periods to a given period. If the number is negative, it will be substracted from the given period.
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
This function is useful for moving forward or backward in time for forecasting, rolling budgets, or comparative period analysis, especially in custom fiscal calendars.
ADDPERIOD(data, number, max)
Example:
data = "2019-01"
ADDPERIOD(data, 2) = "2019-03"
Returns a period relative to the period selected by the user in response to a SELECTPERIOD function, or relative to today's date.
This function is useful for dynamic time references in models and reports, such as "last period", "same period last year", or "next quarter".
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
"NOW.PER" =
today's period
"NOW.PREV" = previous period to today
"NOW.NEXT" = next period to
today
"NOW.PREV.dd" = previous period to today, up until the given day
"NOW.NEXT.dd"
= next period to today, beyond the given day
Examples:
GETPERIOD() returns the period selected by the user.
GETPERIOD("PREV")
returns the previous period based on the selection by the user.
GETPERIOD("NOW.PER")
returns the period based on today's date.
GETPERIOD("NOW.PREV") returns the previous period
based on today's date.
GETPERIOD("NOW.PREV.10") returns the previous period based on today's
date up until the 10th day of this month. Beyond the 10th day, it will return the current
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.
This function is useful for transforming imported dates into reporting periods for aggregation, filtering, and time-based analysis.
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
Converts the given date to a period name (YYYY-MM) using a week-based accounting calendar.
XLReporting will calculate the period number by comparing the given date to the given start date of a financial year, applying the given type of week calendar. The function allows the start of financial years that fall a couple of days before or after January 1st.
Alternatively, if you pass a period name, the function will convert this to a text string with the start date and end date (YYYY-MM-DD) of that given period, separated by a comma. If you want to split the dates, you can use the SPLIT function, or use the SUBSTITUTE function to replace the comma with another delimiter (e.g. in import connectors).
This function is useful for retail or manufacturing reporting where week-based accounting periods (e.g. 4-4-5, 4-5-4) are common, aligning operations to business cycles.
PERIOD(data, start, type)
type:
"445" = 4-4-5 periods (default)
"454" = 4-5-4 periods
"544" -
5-4-4 periods
Examples:
data = "2019-01-29"
PERIOD445(data, "2019-01-01") =
2019-02
PERIOD445(data, "2018-12-31", "454") = 2019-02
PERIOD445("2019-01",
"2019-01-01") = 2019-01-01,2019-01-28
PERIOD445("2019-01", "2018-12-31") =
2018-12-31,2019-01-27
Returns a friendly name for the given period.
If you set a fiscal year via the SELECTPERIOD function, the fiscal year can be included in the period name.
You can also set the starting period of a fiscal year as the type parameter to combine both years in the period name.
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
This function is useful for making reports user-friendly by translating internal period codes like "2023-01" into "Jan 2023" or "Q1 FY23".
PERIODNAME(data, type, max)
type:
"Y" = fiscal year (default)
"Q" = fiscal quarter (YYYY QQ)
"P"
= fiscal period (YYYY PP)
"U" = formatted period as per user profile setting
"W" = week
number (YYYY WW)
"I" = week number (YYYY WW) as per ISO 8601
"M" = month name (MMM
YYYY)
"L" = long month name (MMMMMM YYYY)
"C" = calendar month (without year)
"N" =
period number (without year)
2-15 = fiscal year start
Examples:
data = "2019-08"
PERIODNAME(data, "Y") = "2019"
PERIODNAME(data,
"Q") = "2019 Q3"
PERIODNAME(data, "M") = "Aug 2019"
PERIODNAME(data, 6) = "2019/20"
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 given period falls within the range of periods, and return either that period (if it exists) or an empty value (if it does not exist).
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
You can use the returned value by itself or as a condition inside an IF formula.
This function is useful for creating sequences for time-series models, rolling forecasts, and dashboards with dynamic period selection.
PERIODRANGE(data, number, interval, period, max)
Examples:
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"
Calculates the number of periods between the 2 given periods. You can either pass dates, periods (YYYY-MM), or years (YYYY) as parameters.
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
This function is useful for determining elapsed time in months or custom-defined periods, crucial for time-based accruals, forecasts, or YTD calculations.
PERIODS(data, period, max)
Example:
data = "2019-01"
PERIODS(data, "2019-03") = 2
Returns the total sum of amounts 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 in
selecting and calculating the appropriate period amounts 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
amounts of 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.
This function is useful for aggregating values by reporting periods (YTD, current month, rolling, etc.) and enables interactive reports when used with SELECTPERIOD.
You can also watch the tutorial video.
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 (= closing balance of preceding 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.OPEN" = opening balance last year (= closing balance of preceding year)
"LY.MOV" = movement this period 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)
"ROLL" = rolling number of periods, relative to (and including) the selected period
(you must call the function as PERIODSUM(data, type, condition, periods) and specify the number
of periods as positive for future periods, or negative for past periods)
"LY.ROLL" = rolling number of periods, relative to (and including) the selected period in
the last year (you must call the function as PERIODSUM(data, type, condition, periods) and
specify the number of periods as positive for future periods, or negative for past periods)
"FR.TO" = from to period (you must call the function as PERIODSUM(data, type, condition,
from, to) and specify the from and to period either as YYYY-MM or as a number from 1 to 12 in
which case XLReporting will apply the selected year)
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,
"ROLL", "", 3) returns the sum for 2019-03 to 2019-05
PERIODSUM(data, "FR.TO", "",
"2019-02", "2019-03") returns the sum for 2019-02 to 2019-03
PERIODSUM(data, "FR.TO", "", 2,
3) returns the sum for 2019-02 to 2019-03 assuming the selected period falls in 2019
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 (or both) the from or to parameter, they will be assumed to coincide with the start respectively end of the given period range.
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
This function is useful for evaluating budget coverage or time allocation across user-defined period spans.
PERIODSWITHIN(data, period, from, to, max)
Example:
PERIODSWITHIN("2019-01", "2019-03", "2019-02", "2019-04") = 2
Calculates the year-to-date proportion of the given amount, based on the given year-to-date period and the number of periods that the given from and to periods fall within the given year.
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
This function is useful for proportional spread of annual budgets, amortizations, or progress toward targets.
PERIODYTD(data, year, period, from, to, max)
Example:
PERIODYTD(100, "2023", "2023-04", "2023-03", "2023-05") = 66.667
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 select and calculate the appropriate amounts based on the selected period. You need to place PERIODSUM on a field containing periods (YYYY-MM).
The SELECTPERIOD function can be used in different ways, from simple defaults to sophisticated logic:
Selecting the source
The source parameter can be a field in a data set (e.g. [Periods:
Period]), a static list of comma-separated values (e.g. "2020-01,2020-02,2020-03"), or an
expression that returns a list of values (e.g. PERIODRANGE("2020", 12).
If you use the source parameter, you can also generate a list of years (YYYY), for example using the YEARRANGE() function. This will present a list of years to the user, whilst the period amounts will be calculated as if the last month in the year (YYYY-12) had been selected.
Selecting the filter
The filter parameter must contain a field in a data set with an operator
and value to match against (e.g. [Periods: Closed] = "N"). You can also use these filter
functions: BETWEEN, CONTAINS, ISANY, ISBLANK, AND, OR, NOT. For more information, see filter functions.
This function is useful for allowing interactive selection of reporting periods in models and dashboards, with support for fiscal year logic.
You can also watch the tutorial video.
SELECTPERIOD(start, source, filter)
start:
1 - 12 = the 1st month of the fiscal year (optional, 1 = default)
Examples:
SELECTPERIOD() prompts the user to select a period (mandatory)
SELECTPERIOD(4)
prompts the user to select a period (mandatory), with each fiscal year starting in April.
SELECTPERIOD([Periods:
Period]) shows a custom list of periods, with the fiscal year starting in January.
SELECTPERIOD(PERIODRANGE(2020,12))
shows a predefined list of periods, with the fiscal year starting in January.
Returns 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.
Instead of passing a full period (YYYY-MM), a year (YYYY), or a date in the data parameter, you can also pass a number from -2 to +2 relative to the current year based on today's date (from 2 years before the current year up to 2 years after the current year). This will return the given period in that year.
By default, this function assumes there are maximum 12 periods in a year (effectively calendar months within a year). You can specify the optional max parameter if you want the function to calculate with a different number of periods per year.
This function is useful for navigating within a fiscal year, such as setting beginning or end periods for custom reports or time slicing.
SETPERIOD(data, period, start, max)
Examples:
data = "2019-04"
SETPERIOD(data, 12) = "2019-12"
SETPERIOD(data,
12, 7) = "2019-06"
SETPERIOD(0, 5) = "2019-05"
Returns the period based on today's date, plus or minus the given number of periods (optional).
This function is useful for anchoring models to a rolling time horizon, e.g. current month or 3 months ago.
THISPERIOD(number)
Examples:
today = 10-dec-2019
THISPERIOD() = 2019-12
THISPERIOD(-6) =
2019-06
Returns the year based on today's date, plus or minus the given number of years (optional).
This function is useful for creating dynamic time references for comparative reports, projections, or fiscal year navigation.
THISYEAR(number)
Examples:
today = 10-dec-2019
THISYEAR() = 2019
THISYEAR(-1) =
2018