How to Use PREVIOUSVALUE and NEXTVALUE Expressions in Bold BI
Use PreviousValue and NextValue expressions to retrieve a value from the previous or next row and compare it with the current row. These expressions are commonly used for trend analysis, difference calculations, and change detection scenarios in Bold BI expression columns.
Step-by-Step Guide
-
Open the dashboard in Bold BI Dashboard Designer
-
Navigate to Data Design View
- Click on the datasource icon then click on the edit icon to open the data source associated with the dashboard.
- Add an expression column
-
In the datasource designer page, click on the Expression icon and create expression columns to evaluate PreviousValue and NextValue.
-
Provide a name for the expression (for example, PrevSales, SalesChange, or NextOrderDate).
Important:
PreviousValue and NextValue are row-based expressions. Their evaluation depends on the order of rows in the dataset.
Ensure the data is sorted consistently, typically by a date column or a unique key, to obtain accurate and predictable results.
- Save and use the expression in a widget
- Click Save to store the expression.
- Drag the newly created expression field into a supported widget (such as a grid, KPI card, or chart).
- Preview the dashboard to validate the calculated results.
Code Samples
- Retrieve the previous row’s value
PREVIOUSVALUE([Sales], 1)
Note:
The offset parameter specifies how many rows to move backward from the current row.
An offset value of 1 retrieves the immediately previous row.
Use case: Display the previous period’s sales value as a separate measure.
2. Retrieve the next row’s value
NEXTVALUE([Sales], 1)
Use case: Compare the current value with the next sequential value (useful in forward-looking or sequence-validation scenarios).
3. Calculate the absolute difference from the previous row
[Sales] - PREVIOUSVALUE([Sales], 1)
- Calculate percentage change from the previous row
([Sales] - PREVIOUSVALUE([Sales],1)) / PREVIOUSVALUE([Sales], 1)
To safely handle NULL or zero values:
IF(
ISNULL(PREVIOUSVALUE([Sales])) OR PREVIOUSVALUE([Sales]) = 0,
NULL,
([Sales] - PREVIOUSVALUE([Sales])) / PREVIOUSVALUE([Sales])
)
- Detect a change in a dimension value compared to the previous row
IF([Status] <> PREVIOUSVALUE([Status]), 1, 0)
Use case: Identify breaks in a sequence, such as status transitions or category changes.
Related Articles
