Identifiers
Identifiers in Plan are predefined references that allow you to access specific rows, columns, hierarchy levels, periods, and other contextual information in formulas. They simplify calculations by dynamically referring to report elements instead of hardcoding values. Use the following identifiers to build formulas that respond to the current report context.
CLOSEDPERIOD
The CLOSEDPERIOD identifier returns TRUE if the referenced forecast period is closed and FALSE if the period is open. It is commonly used with conditional functions such as IF to perform different actions for closed and open forecast periods. To refer to the open periods, use the NOT operator along with the CLOSEDPERIOD function.
Syntax
[Forecast].CLOSEDPERIODExample
The following example checks whether a forecast period is closed. If the period is closed, the formula returns Closed. Otherwise, it returns the forecast value rounded to two decimal places with a small adjustment.
IF([Forecast].CLOSEDPERIOD, "Closed", ROUND([Forecast] + 0.05, 2))
COLUMN.CURRENT_PERIOD
The COLUMN.CURRENT_PERIOD identifier returns the current period represented by the active column in the report. You can use this identifier with date-based functions, such as SHIFT, MOVINGSUM, and MOVINGAVERAGE, to create dynamic time-based calculations that automatically adjust based on the current column in the hierarchy.
Syntax
Examples
The following example uses COLUMN.CURRENT_PERIOD with the SHIFT function to return the date two months after the current period.
The formula shifts each month's date forward by two months. For example, January 2025 returns 3/1/2025 and February 2025 returns 4/1/2025.
The following example uses COLUMN.CURRENT_PERIOD with the MOVINGSUM and SHIFT functions to calculate the total sales from the current period through the next two months.
The COLUMN.CURRENT_PERIOD specifies the start of the calculation range, and SHIFT(COLUMN.CURRENT_PERIOD, "2M") specifies the end of the range. When the current period is January 2025, the moving sum includes the sales for January, February, and March. As the calculation moves across the report, the date range updates automatically for each column.
COLUMN.DATE
The COLUMN.DATE identifier returns the date derived from the date hierarchy of the current column. You can use this identifier to retrieve the date represented by a column header, such as Year, Quarter, or Month, and use it in calculations or display it in a measure.
Syntax
Example
The following example creates a measure that returns the date represented by the current column's date hierarchy.
When the column hierarchy contains date members such as Year, Quarter, and Month, the identifier returns the corresponding date for the current column.
Note
COLUMN.DATE can only be used with column hierarchies that are based on date dimensions. If the current column does not represent a date hierarchy, the identifier does not return a valid date.
COLUMN.GROUP_INDEX
The GROUP_INDEX identifier returns the position of the current column within a column group. You can use this identifier in both calculated measures and calculated rows to retrieve the index of the current column.
Syntax
Examples
Insert a Formula Measure and use the COLUMN.GROUP_INDEX identifier in the formula. The measure returns the position of each column within the column group.
Insert a Formula Row from Insert Row and use the COLUMN.GROUP_INDEX identifier in the formula. The calculated row returns the position of each column within the column group.
COLUMN.LEVEL
The COLUMN.LEVEL identifier returns the level of the current column in a column hierarchy. It can be used to identify the hierarchy level of each column and perform calculations based on the column position.
Syntax
Example
In this example, the column hierarchy contains Year, Quarter, and Month levels. The identifier returns a numeric value representing the current column level. For example:
Year returns
1.Quarter returns
2.Month returns
3.
You can use the returned level in formulas to perform different calculations or apply conditional logic based on the current column hierarchy level.
COLUMN.PARENT
The COLUMN.PARENT identifier returns the immediate parent of the current column in a column hierarchy. You can use this identifier to reference higher-level members, such as a quarter from a month or a year from a quarter. To navigate multiple hierarchy levels, chain the identifier. For example, COLUMN.PARENT.PARENT returns the grandparent of the current column.
Syntax
To reference higher hierarchy levels, chain the identifier:
Examples
1. Calculate monthly contribution to the quarterly total
The following example calculates the percentage contribution of a month's sales to the total sales for its quarter by dividing the current Sales value by the Sales value of its immediate parent column.
In this example, January is the current column and Q1 is its immediate parent. The formula calculates January's contribution to the total sales for Q1. For example, if January sales are 1,292.70 and Q1 sales are 3,196.82, the result is 40.43%.
2. Calculate monthly contribution to the annual total
The following example calculates the percentage contribution of a month's sales to the total sales for the year by dividing the current Sales value by the Sales value of the grandparent column.
In this example, January is the current column, Q1 is its parent, and 2025 is the grandparent. The formula calculates January's contribution to the annual sales total by referencing the Year column through COLUMN.PARENT.PARENT.
DESCENDANTS
The DESCENDANTS identifier returns an array containing the values of all descendant members of the referenced hierarchy member, including all child levels down to the leaf level. Because the identifier returns an array, you must use it with an aggregate function, such as SUM, MIN, MAX, or AVERAGE, to return a single value.
Syntax
Example
The following example uses the MIN function with the DESCENDANTS identifier to return the minimum sales value from all descendant members of the Audio category.
The DESCENDANTS identifier returns the sales values for all descendant members of the Audio category, including Bluetooth Headphones, Recording Pen, and MP4&MP3. The MIN function evaluates the returned array and displays the smallest value.
FORECAST.CLOSED_END
The CLOSED_END identifier returns the end date of the closed forecast for the referenced forecast. You can use this identifier to retrieve the date through which the forecast is closed and use it in calculations or display it in a measure.
Syntax
Example
The following example returns the end date of the closed forecast for the Forecast measure.
The forecast is closed through December 2025. Therefore, the identifier returns 12/31/2025 for each period in the report.
FORECAST.CLOSED_START
The CLOSED_START identifier returns the start date of the closed forecast period for the referenced forecast. You can use this identifier to determine the first date included in the closed forecast.
Syntax
Example
The following example returns the start date of the closed forecast period for the Forecast measure.
For the referenced forecast, the closed forecast period begins on January 1, 2025. Therefore, the identifier returns 1/1/2025, indicating the first date included in the closed forecast period.
FORECAST.OPEN_END
The OPEN_END identifier returns the end date of the open forecast period for the referenced forecast. You can use this identifier to determine the last date included in the open forecast.
Syntax
Example
The following example returns the end date of the open forecast period for the Forecast measure.
The open forecast period ends on December 31, 2027. Therefore, the identifier returns 12/31/2027, indicating the last date included in the open forecast period.
FORECAST.OPEN_START
The OPEN_START identifier returns the start date of the open forecast period for the referenced forecast. You can use this identifier to determine the first date included in the open forecast.
Syntax
Example
The following example returns the start date of the open forecast period for the Forecast measure.
The open forecast period begins on January 1, 2026. Therefore, the identifier returns 1/1/2026, indicating the first date included in the open forecast period.
HAS
The HAS function checks whether the specified value exists in one or more columns. You can use this function with formula columns and data input columns, such as Single Select and Multi Select.
Syntax
Arguments
[column1, [column2], ...]: A list of columns to search. The list must contain at least one column.searchvalue: The value to search for in the specified columns.
Return value
Returns TRUE if the specified value is found in any of the specified columns. Otherwise, returns FALSE.
Example
The following example checks whether the value Completed exists in the project status columns for the four quarters. The HAS function evaluates the status values and, if any quarter contains Completed, the IF function returns Closed. Otherwise, it returns Yet to close.
HAS_ALL
The HAS_ALL function checks whether all the specified values exist in one or more columns. You can use this function with formula columns and data input columns, such as Single Select and Multi Select.
Syntax
Arguments
[column1, [column2], ...]: A list of columns to search. The list must contain at least one column.[searchvalue1, [searchvalue2], ...]: A list of values to search for. The list must contain at least one value.
Return value
Returns TRUE if all the specified values are found in the specified columns. Otherwise, returns FALSE.
Example
The following example checks whether the values In Review and In Progress exist in the project status columns for the four quarters. The HAS_ALL function evaluates the status values and, if both values are found, the IF function returns On track. Otherwise, it returns Not on track.
HAS_SOME
The HAS_SOME function checks whether one or more of the specified values exist in one or more columns. You can use this function with formula columns and data input columns, such as Single Select and Multi Select.
Syntax
Arguments
[column1, [column2], ...]: A list of columns to search. The list must contain at least one column.[searchvalue1, [searchvalue2], ...]: A list of values to search for. The list must contain at least one value.
Return value
Returns TRUE if one or more of the specified values are found in the specified columns. Otherwise, returns FALSE.
Example
The following example checks whether the values In Review or In Progress exist in the project status columns for the four quarters. If either value is found in any quarter, the HAS_SOME function returns TRUE, and the IF function returns On track. Otherwise, it returns Not on track.
LEAVES
The LEAVES identifier returns the values of all leaf nodes under the selected member as an array. Use this identifier with an aggregate function, such as SUM, MIN, MAX, or AVERAGE, to return a single value.
Syntax
Example
This formula returns the maximum value among all leaf nodes under Audio. The calculated row displays the maximum sales value for each period across all leaf-level members in the Audio hierarchy.
Difference between LEAVES and DESCENDANTS
LEAVES and DESCENDANTS return different sets of members.
DESCENDANTSreturns all descendant members under the selected member, including intermediate hierarchy levels and leaf-level members.LEAVESreturns only the leaf-level members under the selected member.
For example, if Australia contains the hierarchy Audio > Bluetooth Headphones, MP4&MP3, Recording Pen, then:
Australia.DESCENDANTSreturns Audio, Bluetooth Headphones, MP4&MP3, and Recording Pen.Australia.LEAVESreturns Bluetooth Headphones, MP4&MP3, and Recording Pen only.
MATCH
The MATCH function checks whether the specified value exactly matches the value in a column.
Syntax
Arguments
column: The column to search.searchvalue: The value to search for in the specified column.
Return value
Returns TRUE if the specified value exactly matches the value in the column. Otherwise, returns FALSE.
Example
In this example, the MATCH function compares the value in the Q4 Project Status column with Completed. If the values match exactly, the IF function returns Close Sprint; otherwise, it returns BLANK.
MAXDATE
The MAXDATE identifier returns the latest date available in the column header.
Syntax
Example
In this example, the report contains forecast data through 2027, so the MAXDATE identifier returns 12/31/2027 for every row.
MEMBERS
The MEMBERS identifier returns the values of all immediate child members as an array. Use this identifier with an aggregate function, such as SUM, MIN, MAX, or AVERAGE, to return a single value.
Syntax
Example
In this example, the formula returns the minimum value among the immediate child members of Australia. The immediate child members are the product categories under Australia, and the calculated row displays the minimum sales value for each month across those categories.
MINDATE
The MINDATE identifier returns the earliest date available in the column header.
Syntax
Example
In this example, the report contains Actuals data from 2025 and forecast data through 2027, so the MINDATE identifier returns 1/1/2025 for every row.
RELATIVE
The RELATIVE identifier converts an absolute cell or column reference into a relative reference.
Syntax
Cell reference
Column reference
Arguments
Cell reference
cell_reference: The cell reference to convert to a relative reference.
Column reference
column: The column reference.offset: The relative position of the target column. Use a negative value to reference a previous column and a positive value to reference a subsequent column.
Return value
Returns the value from the cell or column at the relative position.
Note
Relative references are resolved based on the original report layout. If columns are reordered, the calculation continues to use the original column positions. Relative column references are supported only for visual measures.
Examples
Cell reference
This formula converts the absolute reference to the January Cost value for Audio into a relative reference. Although the formula references January, it automatically retrieves the corresponding month's Audio Cost for each column and adds it to the current row's Cost value.
For example, in the April column, the formula adds April Audio Cost to the current Cost value. Likewise, in the January column, it adds January Audio Cost to the current Cost value.
Column reference
This formula returns the value from the previous Sales column. Since January has no preceding month, IFNA returns 0. For all subsequent months, the formula returns the Sales value from the previous month, which can be used to calculate month-over-month variance.
RELATIVE_COLUMN
The RELATIVE_COLUMN identifier returns the value from a column relative to the current column. It is commonly used to retrieve values from previous columns in a report.
Syntax
Arguments
[Row]: Reference to a row.offset: The number of columns relative to the current column. Use a negative value to reference a previous column.
Return value
Returns the value from the relative column for the specified row.
Example
The following example creates a Premium Support Cost calculated row that displays the previous month's Bluetooth Headphones value for each month. Since the formula uses RELATIVE_COLUMN(-1), each month's value is copied from the immediately preceding month. IFNA returns 0 for January because there is no previous month's value available. Because the formula is added as a template row, the Premium Support Cost row is automatically inserted under every category in the row hierarchy.
ROW.LEVEL
The ROW.LEVEL identifier returns the level of the current row in a row hierarchy. It can be used to identify the hierarchy level of each row and apply calculations or formatting based on the row position.
Syntax
Example
In this example, the row hierarchy contains All, Country, Category, and Subcategory levels. The identifier returns a numeric value representing the current row level. For example:
All returns
1.Country returns
2.Category returns
3.Subcategory returns
4.
You can use the returned level in conditional formulas to apply different calculations or formatting for each hierarchy level.
ROW.PARENT
The ROW.PARENT identifier returns the parent row of the current row. You can chain the identifier to refer to higher levels in the hierarchy, such as the grandparent using ROW.PARENT.PARENT.
Syntax
Example
In this example, the calculated row is inserted below Bluetooth Headphones. The ROW.PARENT.PARENT identifier refers to the Australia row, which is the grandparent of the current row. The formula divides the Bluetooth Headphones sales by the corresponding Australia sales to calculate its percentage contribution.
For January, the Bluetooth Headphones sales are 0.04, Australia sales are 155.56, and the resulting contribution is 0.03%.
THIS.LABEL
The THIS.LABEL identifier returns the labels of the current row hierarchy. When the report contains multiple hierarchy levels, THIS.LABEL can be used to access the label at each level and perform conditional calculations based on row names.
Syntax
Example
In this example, THIS.LABEL is used with the IN function to identify the rows whose labels are Juices or Mineral Water. The SUMIF function then evaluates only the matching rows from the descendants of the All member and returns the combined value for those rows. This allows calculations to be performed based on row labels instead of explicitly referencing individual row members.
Last updated
Was this helpful?