> For the complete documentation index, see [llms.txt](https://docs.fabricplan.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.fabricplan.com/planning-sheets/reference/formula-syntax/identifiers/identifiers.md).
# 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].CLOSEDPERIOD
```
#### Example
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
```
COLUMN.CURRENT_PERIOD
```
#### Examples
The following example uses *COLUMN.CURRENT\_PERIOD* with the `SHIFT` function to return the date two months after the current period.
```
SHIFT(COLUMN.CURRENT_PERIOD, "2M")
```
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.
```
MOVINGSUM([Sales], COLUMN.CURRENT_PERIOD, SHIFT(COLUMN.CURRENT_PERIOD, "2M"))
```
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
```
COLUMN.DATE
```
#### Example
The following example creates a measure that returns the date represented by the current column's date hierarchy.
```
COLUMN.DATE
```
When the column hierarchy contains date members such as **Year**, **Quarter**, and **Month**, the identifier returns the corresponding date for the current column.
{% hint style="info" %}
#### 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.
{% endhint %}
### 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
```
COLUMN.GROUP_INDEX
```
#### Examples
COLUMN.GROUP_INDEX
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
```
COLUMN.LEVEL
```
#### Example
```
COLUMN.LEVEL
```
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
```
COLUMN.PARENT.[]
```
To reference higher hierarchy levels, chain the identifier:
```
COLUMN.PARENT.PARENT.[]
```
#### 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.
```
[Sales] / COLUMN.PARENT.[Sales]
```
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.
```
[Sales] / COLUMN.PARENT.PARENT.[Sales]
```
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
```
[HierarchyMember].DESCENDANTS
```
#### 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.
```
MIN([Audio].DESCENDANTS)
```
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
```
[Forecast].CLOSED_END
```
#### Example
The following example returns the end date of the closed forecast for the **Forecast** measure.
```
[Forecast].CLOSED_END
```
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
```
[Forecast].CLOSED_START
```
#### Example
The following example returns the start date of the closed forecast period for the **Forecast** measure.
```
[Forecast].CLOSED_START
```
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
```
[Forecast].OPEN_END
```
#### Example
The following example returns the end date of the open forecast period for the **Forecast** measure.
```
[Forecast].OPEN_END
```
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
```
[Forecast].OPEN_START
```
#### Example
The following example returns the start date of the open forecast period for the **Forecast** measure.
```
[Forecast].OPEN_START
```
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
```
HAS([column1, [column2], ...], searchvalue)
```
#### 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`.
```
IF(HAS([[2025].[Q1].[Status], [2025].[Q2].[Status], [2025].[Q3].[Status], [2025].[Q4].[Status]], "Completed"), "Closed", "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
```
HAS_ALL([column1, [column2], ...], [searchvalue1, [searchvalue2], ...])
```
#### 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`.
```
IF(HAS_ALL([[2025].[Q1].[Status], [2025].[Q2].[Status], [2025].[Q3].[Status], [2025].[Q4].[Status]], ["In Review", "In Progress"]), "On track", "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
```
HAS_SOME([column1, [column2], ...], [searchvalue1, [searchvalue2], ...])
```
#### 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`.
```
IF(HAS_SOME([[2025].[Q1].[Status], [2025].[Q2].[Status], [2025].[Q3].[Status], [2025].[Q4].[Status]], ["In Review", "In Progress"]), "On track", "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
```
.LEAVES
```
#### Example
```
MAX([Audio].LEAVES)
```
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.
* `DESCENDANTS` returns all descendant members under the selected member, including intermediate hierarchy levels and leaf-level members.
* `LEAVES` returns 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.DESCENDANTS` returns **Audio**, **Bluetooth Headphones**, **MP4\&MP3**, and **Recording Pen**.
* `Australia.LEAVES` returns **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
```
MATCH(column, searchvalue)
```
#### 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
```
IF(MATCH([2025].[Q4].[Project Status], "Completed"), "Close Sprint", BLANK)
```
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
```
MAXDATE
```
#### Example
```
MAXDATE
```
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
```
.MEMBERS
```
#### Example
```
MIN([Australia].MEMBERS)
```
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
```
MINDATE
```
#### Example
```
MINDATE
```
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
```
RELATIVE(cell_reference)
```
#### Column reference
```
[column].RELATIVE(offset)
```
#### 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.
{% hint style="info" %}
#### 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.
{% endhint %}
#### Examples
#### Cell reference
```
RELATIVE([[Audio], [Jan].[Cost]]) + [Cost]
```
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
```
IFNA([Sales].RELATIVE(-1), 0)
```
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
```
[Row].RELATIVE_COLUMN(offset)
```
#### 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
```
IFNA([Bluetooth Headphones].RELATIVE_COLUMN(-1), 0)
```
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
```
ROW.LEVEL
```
#### Example
```
ROW.LEVEL
```
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
```
ROW.PARENT
```
#### Example
```
[Australia].[Audio].[Bluetooth Headphones] / ROW.PARENT.PARENT
```
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
```
THIS.LABEL
```
#### Example
```
SUMIF([All].DESCENDANTS, IN(THIS.LABEL, ['Juices', 'Mineral Water']))
```
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.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://docs.fabricplan.com/planning-sheets/reference/formula-syntax/identifiers/identifiers.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
