FILLMISSING

Overview

Description	Fills missing level values of a specified dimension into the input node's data. The input node must already contain the levels of that dimension. Use when you want to fill structurally missing level values in an existing dimension with a constant value.
Syntax	`FILLMISSING('Node' [, "Dimension" [, "Value" [, "Behavior"]]])`
Parameters	Node: A node with potentially missing level values. Dimension: Name of the dimension whose level values will be used to fill the input node. This function does not add levels to the input; it only fills missing level values of levels already present. The default fill is done on the finest time level. Value: Value written into newly filled rows. The default value is 0. Behavior (optional): The filling behavior that the function will follow. Available behaviors are: `"All"`: Default behavior. Fills all empty values with the specified value over the horizon. `"StartAtFirst"`: Fills only empty entries with the specified value after the first entry of the specified dimension. `"FinishAtLast"`: Fills only empty entries from the start of the project horizon until the last entry of the specified dimension. `"Interval"`: Fills only empty entries between the first and last present values.
Limitations	Node must already contain levels of the specified dimension. If the node contains linked levels, it is not possible to fill values of the dimension that is linked to. Fill the values of the dimension that is linked from instead to get all valid values. Important: This function ensures that the links from the levels of the specified dimension match those in dimension management. Do not use this function if you want the node to have different level links than the dimensions.

Examples

Fill missing values across the time horizon

This example fills missing year and product values with a constant. The project time horizon is 2025-2028.

The dimension "ProductDimension" consists of the level "Product" and the level values "Car", "Van", and "Truck".

The input node features the level values "2025", "2027" and "Car", "Van".

Input node: Input node

Year	Product	Value
2025	Car	100
2027	Van	90

Formula: FILLMISSING('Input node')

Year	Product	→ FILLMISSING Result
2025	Car	100
2025	Van	0
2026	Car	0
2026	Van	0
2027	Car	0
2027	Van	90
2028	Car	0
2028	Van	0

With this formula, the input node is filled with the missing level values "2026" and "2028" of the default dimension "Time", and the default value 0 is added.

The input node now features all years of the project time horizon with the value 0.

Formula: FILLMISSING('Input node', "ProductDimension", 1)

Year	Product	→ FILLMISSING Result
2025	Car	100
2025	Van	1
2025	Truck	1
2027	Car	1
2027	Van	90
2027	Truck	1

With this formula, the input node is filled with the missing level values of the dimension "ProductDimension" such as "Truck". The entered value is 1.

Fill missing values with different behaviors

This example uses the same input with different behavior settings to show how the fill range changes.

Input node: Input node

Year	Product	Value
2026	Car	100
2027	Van	200
2028	Car	300

Formula: FILLMISSING('Input node', "Time", "1", "All")

Year	Product	→ FILLMISSING Result
2025	Car	1
2026	Car	100
2027	Car	1
2028	Car	300
2025	Van	1
2026	Van	1
2027	Van	200
2028	Van	1

When using the default behavior "All", the function fills the empty values over the project horizon.

Formula: FILLMISSING('Input node', "Time", "1", "StartAtFirst")

Year	Product	→ FILLMISSING Result
2026	Car	100
2027	Car	1
2028	Car	300
2027	Van	200
2028	Van	1

When using the behavior "StartAtFirst", the function fills the empty entries starting after the first value present for each product. Filling ends at the end of the project horizon.

Formula: FILLMISSING('Input node', "Time", "1", "FinishAtLast")

Year	Product	→ FILLMISSING Result
2025	Car	1
2026	Car	100
2027	Car	1
2028	Car	300
2025	Van	1
2026	Van	1
2027	Van	200

When using the behavior "FinishAtLast", the function fills the empty values until the last entry present for each product. The filling starts at the start of the project horizon.

Formula: FILLMISSING('Input node', "Time", "1", "Interval")

Year	Product	→ FILLMISSING Result
2026	Car	100
2027	Car	1
2028	Car	300
2027	Van	200

When using the behavior "Interval", the function fills the empty values starting at the first entry present and finishing at the last entry present for each product.

Fill missing values with linked levels

Keep in mind that this function might change existing level values if the levels are linked. The result of this function always follows the links in the dimension table.

Input node: Input

Month	CalendarMonth	Value
2025-02	06	100
2025-03	06	105
2025-04	11	110
2025-05	05	115
2025-06	06	120
2025-07	02	125

Formula: FILLMISSING('Input', "TimeDimension", 15)

Month	CalendarMonth	→ FILLMISSING Result
2025-01	01	15
2025-02	02	100
2025-03	03	105
2025-04	04	110
2025-05	05	115
2025-06	06	120
2025-07	07	125
2025-08	08	15
2025-09	09	15
2025-10	10	15
2025-11	11	15
2025-12	12	15

Related Functions

Function	When to use instead
FILLMISSING_LAST	When you want missing values filled using the last available value instead of a constant.
FILL_NA	When you want to fill N/A values on existing level combinations after pivoting instead of adding missing level values for a dimension.