DECOMPOSE OpenBI
A time series' trend, season and remainder (STL, as R's stl), or how strong each is.
Group: Statistics. OpenBI’s own: DAX has no function of this name.
Syntax
DECOMPOSE ( <table>, <date>, <value> [, <period>] [, <model>] [, <what>] [, <seasonalWindow>] )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows of the series: one row an observation. |
date | Expression evaluated for each row | The date of each row, or a period's number (1, 2, 3 …). |
value | Expression evaluated for each row | The number observed, for each row. |
period | Whole number optional | How many observations make one cycle: 12 for months in a year, 4 for quarters, 7 for days in a week. Left out or blank, the dates' spacing decides. |
model | Text optional | "additive" or "multiplicative"; "auto", the default, picks multiplicative if all the values are positive. |
what | Text optional | "components" (the default) for a row an observation, or "stats" for a single row of shares and strengths. |
seasonalWindow | Whole number optional | An odd number of cycles over which the seasonal pattern may change. Left out or blank, it is the same in every cycle (periodic). |
Returns
Table. With "components": Date, Data, Trend, Seasonal and Remainder, a row an observation in date order. With "stats": one row with the Model and Period used, a share of the variation for each component (Seasonal share, Trend share, Remainder share), the Seasonal strength and Trend strength, the Seasonal window and the number of Observations.
Remarks
- The method is STL, seasonal-trend decomposition by Loess. With the periodic default the components are R's stl with s.window = "periodic". With a seasonal window the season is smoothed with local lines, as statsmodels' STL does (R's stl with s.degree = 1).
- Without a period, the dates' usual spacing sets it: 12 for months, 4 for quarters, 52 for weeks, and for days 7, or 5 when no date falls on a weekend. With numbers instead of dates, give the period.
- Additive: Data = Trend + Seasonal + Remainder. Multiplicative: the series is split on its logarithms, and Data = Trend × Seasonal × Remainder, the Trend in the data's units, Seasonal and Remainder as factors around 1.
- The shares are each component's variance over the sum of the three. A strength is one minus the remainder's variance over the variance of that component and the remainder added together, never below 0 (Hyndman's measure): near 1, a strong season or trend.
- At least two full cycles are needed. The multiplicative model takes values above 0 only. Rows with a blank date or value are left out, and the rest sorted by date.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
Examples
How seasonal are the monthly sales?
EVALUATE
SELECTCOLUMNS (
DECOMPOSE ( VALUES ( Sales[Month] ), Sales[Month], [Liters sold], BLANK (), BLANK (), "stats" ),
"Model", [Model], "Period", [Period],
"Seasonal strength", [Seasonal strength], "Trend strength", [Trend strength],
"Seasonal share", [Seasonal share], "Trend share", [Trend share], "Remainder share", [Remainder share]
)| Model | Period | Seasonal strength | Trend strength | Seasonal share | Trend share | Remainder share |
|---|---|---|---|---|---|---|
| multiplicative | 12 | 0.985395484346268 | 0.738208221420706 | 0.94772085958627 | 0.0384279338838701 | 0.0138512065298596 |
The components of 2026
Every month sells more than nothing, so the model is multiplicative: July's season is about 1.31, 31 % above the trend.
EVALUATE
FILTER (
DECOMPOSE ( VALUES ( Sales[Month] ), Sales[Month], [Liters sold] ),
YEAR ( [Date] ) = 2026
)| Date | Data | Trend | Seasonal | Remainder |
|---|---|---|---|---|
| 2026-01-01 | 45549 | 50919.068666266 | 0.894346844371219 | 1.00021281877895 |
| 2026-02-01 | 45985 | 51013.3875471212 | 0.892454955051518 | 1.01005662565473 |
| 2026-03-01 | 45109 | 51107.8811375993 | 0.890452131388649 | 0.991207879324482 |
| 2026-04-01 | 48416 | 51217.6870114036 | 0.909342785772619 | 1.0395402723476 |
| 2026-05-01 | 52329 | 51327.7288043979 | 1.01088064112508 | 1.00853391721795 |
| 2026-06-01 | 59638 | 51438.3265150654 | 1.1633799013007 | 0.996585773404576 |
| 2026-07-01 | 66423 | 51549.1625346137 | 1.30633579346739 | 0.986374976094684 |
| 2026-08-01 | 60351 | 51651.0327080353 | 1.1775193862979 | 0.992287214339843 |
| 2026-09-01 | 50549 | 51753.1041947611 | 0.986095707958469 | 0.990505965927782 |
| 2026-10-01 | 48953 | 51848.3600645675 | 0.936880668795958 | 1.00776671345617 |
Additive, with a season that may change
EVALUATE
SELECTCOLUMNS (
DECOMPOSE ( VALUES ( Sales[Month] ), Sales[Month], [Liters sold], 12, "additive", "stats", 7 ),
"Model", [Model], "Seasonal window", [Seasonal window],
"Seasonal strength", [Seasonal strength], "Trend strength", [Trend strength]
)| Model | Seasonal window | Seasonal strength | Trend strength |
|---|---|---|---|
| additive | 7 | 0.995341555749767 | 0.890953279230405 |