Formula function reference

The formula language is shared across all cubes. Arguments are separated by semicolons (;). Numeric item codes and codes with special characters must be quoted — e.g. "620200" + "22520", "R&D" + "G&A". Functions are grouped by category below.

Aggregate

Function	What it does	Example
`Average(val1; val2; ...)`	Arithmetic mean.	`Average(Q1; Q2; Q3; Q4)`
`Count(val1; val2; ...)`	Count of non-empty args.	`Count(Q1; Q2; Q3; Q4)`
`Max(val1; val2; ...)`	Maximum of args.	`Max(Floor; Revenue)`
`Min(val1; val2; ...)`	Minimum of args.	`Min(Cap; Sales)`
`SetAverage([pattern; ...])`	Average over sibling-item pattern matches.	`SetAverage("60*")`
`SetCount([pattern; ...])`	Count of sibling-item pattern matches.	`SetCount("60*")`
`SetMax([pattern; ...])`	Maximum across sibling-item pattern matches.	`SetMax("60*")`
`SetMin([pattern; ...])`	Minimum across sibling-item pattern matches.	`SetMin("60*")`
`SetSum([pattern; ...])`	Sum over sibling-item pattern matches.	`SetSum("60*")`

Math

Function	What it does	Example
`Abs(value)`	Absolute value.	`Abs(Revenue - Budget)`
`Cos(angle)`	Cosine (radians).	`Cos(Angle)`
`Exp(value)`	e raised to the argument.	`Exp(Rate * Time)`
`FV(rate; nper; pmt)`	Future value of annuity.	`FV(0.05; 10; -1000)`
`Log(value; [base])`	Natural log; with second arg, log base n.	`Log(GrowthFactor)`
`Pi()`	The constant π.	`2 * Pi() * Radius`
`Pow(base; exponent)`	Raise first arg to power of second.	`Pow(1 + Rate; Periods)`
`Round(value)`	Round to nearest integer.	`Round(Sales * 1.075)`
`Sin(angle)`	Sine (radians).	`Sin(Pi() / 4)`
`Sqrt(value)`	Square root.	`Sqrt(Variance)`
`Tan(angle)`	Tangent (radians).	`Tan(Angle)`

Logic

Function	What it does	Example
`And(val1; val2; ...)`	Logical AND across all args.	`And(Sales > 0; Margin > 0)`
`Choose(index; val1; val2; ...)`	Pick first non-empty argument.	`Choose(Tier; Bronze; Silver; Gold)`
`If(condition; trueValue; falseValue)`	Conditional: if(cond; then; else).	`If(Sales > 0; Sales * Margin; 0)`
`Not(value)`	Logical NOT.	`Not(IsActive)`
`Or(val1; val2; ...)`	Logical OR across all args.	`Or(IsNew; IsRecurring)`

Time Series (requires a time-scale dimension)

Function	What it does	Example
`Decumulate(item)`	Current minus previous period.	`Decumulate(CumulativeRevenue)`
`Feed(item)`	Previous period value. Feed(self) reads this same line at the prior data period.	`Feed(self)`
`FeedFrom(balance; [opening])`	Previous period balance (override first period with data). FeedFrom(self) and FeedFrom(self; opening) read this same line at the prior data period.	`FeedFrom(self; OpeningBalance)`
`FirstPeriod()`	1 if first period, else 0.	`FirstPeriod()`
`Grow(rate; value; [opening])`	Compound growth. Grow(rate; value) — period 0 returns value. Grow(self; rate) — grows this line from its own prior period (use the period-0 cell override as the opening). Grow(self; rate; opening) — explicit opening (literal, item ref, or Reference()).	`Grow(self; 0.05)`
`GrowFrom(rate; value)`	Growth from base value.	`GrowFrom(0.05; OpeningRevenue)`
`Lag(item; [shift])`	Value at shifted period. self is NOT supported here — use Prior(self; n) for same-line lookback.	`Lag(Revenue; 1)`
`LastPeriod()`	1 if last period, else 0.	`LastPeriod()`
`NumPeriods()`	Count of data periods.	`NumPeriods()`
`PctChange(item; [shift])`	(curr - lagged) / \|lagged\|; shift defaults to -1.	`PctChange(Revenue)`
`Prior(name; [n])`	Value at the n-th previous DATA period (skips formula items like Quarter/Year). Accepts a same-cube item code, a Reference varName for cross-cube lookup, or `self` to read this same line. n defaults to 1.	`Prior(self; 1)`
`PriorDelta(name; [n])`	Current minus Prior(name; n) — period-over-period change, skipping aggregates. Same name resolution as Prior.	`PriorDelta(Headcount)`
`RollingAvg(item; [N])`	Average of last N periods.	`RollingAvg(Sales; 4)`
`RollingSum(item; [N])`	Sum of last N periods (inclusive).	`RollingSum(Sales; 3)`
`YearToDate(value)`	Year-to-date cumulative sum.	`YTD(Revenue)`
`YoY(item)`	Value at the same period one year ago (auto-sized by time dim periodType). YoY(self) reads this same line at the prior year.	`YoY(self)`
`YoYDelta(item)`	Current minus same period last year.	`YoYDelta(Revenue)`
`YoYPct(item)`	(curr - YoY) / \|YoY\| — year-over-year % change.	`YoYPct(Revenue)`

Cross-cube

Function	What it does	Example
`Reference(referenceName)`	Named cross-reference lookup from another cube.	`Reference("AR") / 365`

Misc

Function	What it does	Example
`Between(value; low; high)`	True when first arg is between second and third (inclusive).	`Between(Age; 18; 65)`
`Difference(a; b)`	Signed difference between two values.	`Difference(Actual; Forecast)`
`In(value; item1; item2; ...)`	True when the first arg matches any subsequent literal value.	`In(Region; "EMEA"; "APAC")`
`Remain(a; b)`	Remainder after subtraction-with-clamp.	`Remain(Available; Demand)`

Technical signatures (argument types — for MCP & advanced use)

Function	Typed signature
Abs	`Abs(value: number)`
And	`And(val1: number; val2: number; ...)`
Average	`Average(val1: number; val2: number; ...)`
Between	`Between(value: number; low: number; high: number)`
Choose	`Choose(index: number; val1: number; val2: number; ...)`
Cos	`Cos(angle: number)`
Count	`Count(val1: number; val2: number; ...)`
Decumulate	`Decumulate(item: item-code)`
Difference	`Difference(a: number; b: number)`
Exp	`Exp(value: number)`
Feed	`Feed(item: item-code)`
FeedFrom	`FeedFrom(balance: item-code; [opening: expression])`
FirstPeriod	`FirstPeriod()`
FV	`FV(rate: number; nper: number; pmt: number)`
Grow	`Grow(rate: number; value: number \| item-code; [opening: expression])`
GrowFrom	`GrowFrom(rate: number; value: number \| item-code)`
If	`If(condition: number; trueValue: number; falseValue: number)`
In	`In(value: number; item1: literal-string; item2: literal-string; ...)`
Lag	`Lag(item: item-code; [shift: number])`
LastPeriod	`LastPeriod()`
Log	`Log(value: number; [base: number])`
Max	`Max(val1: number; val2: number; ...)`
Min	`Min(val1: number; val2: number; ...)`
Not	`Not(value: number)`
NumPeriods	`NumPeriods()`
Or	`Or(val1: number; val2: number; ...)`
PctChange	`PctChange(item: item-code; [shift: number])`
Pi	`Pi()`
Pow	`Pow(base: number; exponent: number)`
Prior	`Prior(name: item-code \| reference-name; [n: number])`
PriorDelta	`PriorDelta(name: item-code \| reference-name; [n: number])`
Reference	`Reference(referenceName: reference-name)`
Remain	`Remain(a: number; b: number)`
RollingAvg	`RollingAvg(item: item-code; [N: number])`
RollingSum	`RollingSum(item: item-code; [N: number])`
Round	`Round(value: number)`
SetAverage	`SetAverage([pattern: pattern; ...])`
SetCount	`SetCount([pattern: pattern; ...])`
SetMax	`SetMax([pattern: pattern; ...])`
SetMin	`SetMin([pattern: pattern; ...])`
SetSum	`SetSum([pattern: pattern; ...])`
Sin	`Sin(angle: number)`
Sqrt	`Sqrt(value: number)`
Tan	`Tan(angle: number)`
YearToDate	`YearToDate(value: item-code)`
YoY	`YoY(item: item-code)`
YoYDelta	`YoYDelta(item: item-code)`
YoYPct	`YoYPct(item: item-code)`