MONTECARLO OpenBI
A series simulated ahead with QuantLib: thousands of paths from its history, summed up as percentiles, as where they end, or as risk figures.
Group: Quantitative finance. OpenBI’s own: DAX has no function of this name.
Syntax
MONTECARLO ( <history>, <date>, <value>, <steps> [, <output>] [, <paths>] [, <seed>] [, <model>] [, <drift>] [, <volatility>] )| Parameter | Type | Description |
|---|---|---|
history | Table | The history: a row for each date, such as VALUES ( Sales[Month] ), as the filters leave it. |
date | Expression evaluated for each row | The row's date, or a period's number. |
value | Expression evaluated for each row | The row's value: a measure such as liters sold, or a column such as a price. |
steps | Whole number | How many steps to simulate ahead, from 1 to 2000, each the length of the history's typical gap between two dates. |
output | Text optional | "fan" (when left out), "paths", "final" or "stats": which table comes back. |
paths | Whole number optional | How many paths, from 10 to 20000; 5000 when left out, fewer for a very long horizon. |
seed | Whole number optional | Where the random numbers start; 1 when left out. The same seed gives the same paths. |
model | Text optional | "lognormal" (when left out), "meanreverting", "normal" or "bootstrap": how the value moves. |
drift | Number optional | A yearly drift to use instead of the history's, as a decimal; for "normal", in the value's units. |
volatility | Number optional | A yearly volatility to use instead of the history's, as a decimal; for "normal", in the value's units. |
Returns
Table. With "fan": Step and Date, the history in Value, and at each step ahead the paths' P5, P25, P50, P75 and P95 percentiles and their Mean. With "paths": Path, Step, Date and Value, for the first 100 paths. With "final": From, To, Paths and Share, 30 bins of where the paths end. With "stats": one row: when it starts and ends (Start, Date), the end's Mean, P5, P50 and P95 and its Standard deviation, then the Chance below start, Value at risk and Expected shortfall, the figures used (Model, Drift, Volatility, Speed, Level) and the size of the run (Step (years), Steps, Paths, Seed).
Remarks
- Rows where the date or the value is blank are left out; the rest are taken in date order, one value a date. Summarize by the date first when the table has several rows a date.
- The step follows the history: daily (business days when the history has no weekends), weekly, monthly, quarterly, half-yearly or yearly. The dates ahead continue it; with numbered periods, the numbers do.
- The history sets the model's figures: the drift and the volatility a year from its log returns (a period's, with numbered periods); for "meanreverting" the speed and the level it is pulled back to; "bootstrap" draws each step from the history's own returns. Give drift and volatility for a what-if; the bootstrap takes neither.
- "lognormal" moves in proportion and never goes below zero: prices, volumes. "normal" moves by amounts and can: a profit, a cash flow. Every model but "normal" needs values above zero.
- With the same seed the paths are the same, run after run: a report shows the same fan each time. Another seed shows how much the answer owes to chance (0 gives the same paths as 1).
- In "stats", Value at risk is how far the 5th percentile at the end lies below the start, and Expected shortfall how far below the start the worst 5 % of the paths end on average; Chance below start is the share of paths that end below it.
- At least three values of history are needed (four for "meanreverting").
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
Liters sold, a year ahead, in figures
The 36 months of sales, simulated twelve months on from October 2026's 48,953 liters.
EVALUATE
SELECTCOLUMNS (
MONTECARLO ( VALUES ( Sales[Month] ), Sales[Month], [Liters sold], 12, "stats" ),
"Start", [Start],
"Date", [Date],
"P5", ROUND ( [P5], 0 ),
"P50", ROUND ( [P50], 0 ),
"P95", ROUND ( [P95], 0 ),
"Value at risk", ROUND ( [Value at risk], 0 ),
"Drift", ROUND ( [Drift], 4 ),
"Volatility", ROUND ( [Volatility], 4 )
)| Start | Date | P5 | P50 | P95 | Value at risk | Drift | Volatility |
|---|---|---|---|---|---|---|---|
| 48953 | 2027-10-01 | 28281 | 50749 | 91718 | 20672 | 0.1004 | 0.3596 |
The fan, from the last two months on
Steps below 0 are the history, step 0 its last value, and the percentiles widen from there.
EVALUATE
SELECTCOLUMNS (
FILTER ( MONTECARLO ( VALUES ( Sales[Month] ), Sales[Month], [Liters sold], 6 ), [Step] >= -2 ),
"Step", [Step],
"Date", [Date],
"Value", [Value],
"P5", ROUND ( [P5], 0 ),
"P50", ROUND ( [P50], 0 ),
"P95", ROUND ( [P95], 0 )
)| Step | Date | Value | P5 | P50 | P95 |
|---|---|---|---|---|---|
| -2 | 2026-08-01 | 60351 | (blank) | (blank) | (blank) |
| -1 | 2026-09-01 | 50549 | (blank) | (blank) | (blank) |
| 0 | 2026-10-01 | 48953 | 48953 | 48953 | 48953 |
| 1 | 2026-11-01 | (blank) | 41292 | 49072 | 58184 |
| 2 | 2026-12-01 | (blank) | 38664 | 49249 | 62925 |
| 3 | 2027-01-01 | (blank) | 36789 | 49351 | 66147 |
| 4 | 2027-02-01 | (blank) | 35025 | 49601 | 69760 |
| 5 | 2027-03-01 | (blank) | 33856 | 49912 | 72949 |
| 6 | 2027-04-01 | (blank) | 32760 | 50049 | 75664 |
A what-if: a calmer year
The same history with a drift of 5 % and a volatility of 20 % given: the 90 % range narrows.
EVALUATE
SELECTCOLUMNS (
MONTECARLO ( VALUES ( Sales[Month] ), Sales[Month], [Liters sold], 12, "stats", , , "lognormal", 0.05, 0.2 ),
"P5", ROUND ( [P5], 0 ),
"P50", ROUND ( [P50], 0 ),
"P95", ROUND ( [P95], 0 ),
"Drift", [Drift],
"Volatility", [Volatility]
)| P5 | P50 | P95 | Drift | Volatility |
|---|---|---|---|---|
| 36445 | 50451 | 70118 | 0.05 | 0.2 |
Related functions
QUANT.OPTION.PRICE, PERCENTILEX.INC, STDEVX.S, SUMMARIZECOLUMNS.