Configure Time Intelligence Expressions in Bold BI

Time intelligence expressions compare a measure with an earlier calendar period. The PREVIOUSPERIOD function returns the value of an aggregate for a period that is a given number of months before the period of each widget row.

Use PREVIOUSPERIOD to:

• Compare this month’s sales with last month’s sales.
• Calculate the change from the previous month, quarter, or year.
• Calculate a three-month average of earlier months.
• Calculate each group’s share of the previous month’s total.

PREVIOUSPERIOD is listed under the Time Intelligence category in the Expression Designer.

PREVIOUSPERIOD syntax

PREVIOUSPERIOD(<aggregate>([Measure]), [DateColumn], 'MONTH', <offset> [, 'ROW' | 'TABLE'])

Example: PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 1)

Parameters

Supported data sources

PREVIOUSPERIOD is supported for live connections to the following data sources:

• Microsoft SQL Server
• PostgreSQL
• Amazon Redshift

Data sources that use joined tables are supported. For other data sources, the Time Intelligence category is not listed in the Expression Designer.

Add a PREVIOUSPERIOD expression

1. Open the data source and click Expression in the toolbar of the data design view. For more information, see Adding an expression column.
2. Click Add and enter a name, for example Previous Month Amount.
3. Enter the expression, for example PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 1). You can also select PREVIOUSPERIOD from the Time Intelligence category.
4. Click Save. Bold BI validates the expression and shows a message when an argument is not valid. For the messages, see Troubleshooting.

When you select PREVIOUSPERIOD in the Time Intelligence category, the Expression Designer shows its description and syntax.

You can use a saved PREVIOUSPERIOD expression inside other expressions, for example with COALESCE, IF, or arithmetic operators.

Change vs Previous Month = SUM([Amount]) - COALESCE([Previous Month Amount], 0)

Use the expression in a widget

Add the expression to a widget together with the date column used in the expression. The date grouping of that column decides the period of each row.

Week, day of month, day of week, and time of day groupings are not supported.

NOTE: Use the Month Year grouping for month-over-month comparisons. When a row covers one day, the expression returns the total of the whole earlier month.

Calculation scope: ROW and TABLE

The scope decides which rows are added up for the earlier period when the widget contains columns other than the date.

• 'ROW' (default): the earlier period for the same values of the other widget columns, for example the same region.
• 'TABLE': the earlier period for all rows together, ignoring every widget column except the date.

The following expressions use a sample orders data source with the Region, OrderDate, and Amount columns:

Previous Month Amount       = PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 1)
Previous Month Total Amount = PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 1, 'TABLE')
Share of Previous Month     = [Previous Month Amount] / [Previous Month Total Amount]

A grid with OrderDate (Month Year) and Region shows the following values for January to March 2024:

For February 2024, Previous Month Amount is each region’s January amount. Previous Month Total Amount is 2,092 for both regions, which is the January amount of all regions (646 + 1,446). The two shares add up to 1.

When the widget shows only the date column, 'ROW' and 'TABLE' return the same value.

Examples

The examples use the same sample orders data source. The monthly amount of all regions is 2,092 in January 2024, 2,172 in February 2024, 2,252 in March 2024, and 2,332 in April 2024.

Compare with the previous month

Previous Month Amount   = PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 1)
Three Months Ago Amount = PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 3)

A grid with OrderDate (Month Year) shows the following values for January to April 2024:

The sample data starts in January 2024, so the earlier periods of the first months are empty.

Calculate the change from the previous month

Change vs Previous Month = SUM([Amount]) - COALESCE([Previous Month Amount], 0)

A grid with Region filtered to East and OrderDate (Month Year) shows the following values for January to March 2024:

COALESCE turns the empty previous month of January 2024 into 0, so the change is shown instead of an empty value.

Compare with the previous quarter

Group OrderDate by Quarter Year and use an offset of 3:

Previous Quarter Amount = PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 3)

Compare with the previous year

Group OrderDate by Year and use an offset of 12:

Previous Year Amount = PREVIOUSPERIOD(SUM([Amount]), [OrderDate], 'MONTH', 12)

The sample data for 2025 covers January to June, so Sum of Amount for 2025 is a partial year. Previous Year Amount is the total of the whole year 2024.

Filters

Dashboard filters, widget filters, user filters, and URL filters apply to the earlier periods in the same way as to the current period. This applies to both scopes.

For example, with a filter Region = East, Previous Month Total Amount ('TABLE') for February 2024 is 646, the January amount of the East region only. Without the filter, it is 2,092, the January amount of all regions.

A filter on the date column used in the expression only limits the periods that the widget shows. Earlier periods outside the filter are still used. For example, a widget filtered to March 2025 still shows the February 2025 amount as Previous Month Amount.

Missing earlier periods

When an earlier period has no data, PREVIOUSPERIOD returns an empty value (Null). Any calculation with an empty value also returns an empty value, for example [Previous Month Amount] + 100.

Wrap each PREVIOUSPERIOD expression in COALESCE to treat a missing period as 0:

Three Month Average = (COALESCE([Previous Month Amount], 0) + COALESCE([Two Months Ago Amount], 0) + COALESCE([Three Months Ago Amount], 0)) / 3

COALESCE([Previous Month Amount] + [Two Months Ago Amount] + [Three Months Ago Amount], 0) / 3 returns 0 when any one of the three months is missing, because the sum is empty before COALESCE is applied.

Limitations

• Only the 'MONTH' unit is supported, with an offset from 1 to 36. Use 3 for a quarter and 12 for a year.
• The widget must contain the date column used in the expression. A widget without it, such as a number card, shows an error.
• All PREVIOUSPERIOD expressions in one widget must use the same date column.
• Only calendar periods are supported. Fiscal year groupings are not supported.
• The first argument must be one aggregate of a data source column. Another expression cannot be used inside PREVIOUSPERIOD, and PREVIOUSPERIOD cannot be nested.
• PREVIOUSPERIOD cannot be combined with TOTAL, TOTALPERCENTAGE, running functions, PREVIOUSVALUE, NEXTVALUE, level of detail functions (FIXED, INCLUDE, EXCLUDE), window functions, MEDIAN, MODE, or PERCENTILE_CONT in the same expression. A widget that uses PREVIOUSPERIOD cannot also contain level of detail, window, or MEDIAN expressions.
• With the default 'ROW' scope, every other dimension column in the widget is matched with the earlier period. Add numeric columns as measures, such as Sum of Amount. A dimension column whose value changes every period, such as a month label column, returns an empty previous value.
• 'TABLE' scope ignores all widget columns except the date. It cannot keep some columns and ignore others.
• Data sources with data relationships, combined data sources, custom SQL queries that start with a WITH clause, and stored procedures are not supported. Use joined tables instead of data relationships.
• The grid summary row adds up the values shown in the rows. It does not recalculate ratios or averages for the whole period.
• Sorting a widget by a PREVIOUSPERIOD expression column is not supported. Sort by the date column or by another measure.
• In view mode, an expression error shows a general error message. Open the dashboard in the designer to see the detailed message.

Troubleshooting

Related links

Configuring Expression Columns

Aggregation Expressions

Date Expressions

Level Of Detail Expressions

Joining Tables

Configuring Widget Filters