Dataseries expressions

How to write the formula of a calculated series in Axmo — references, filters, arithmetic, aggregation, conditions and missing values.

A dataseries expression is the formula of a calculated series. It is what you write in the Series Editor's formula box, and it is evaluated once at every coordinate the series covers.

A dimensional expression says where to look. A dataseries expression says what to work out.

The two are used together constantly — a filter is a dimensional expression sitting inside a dataseries one — but they are different notations doing different jobs, and it is worth keeping the difference in mind when a formula does not behave.

Referring to another series

Write the name.

Profit = Revenue - Total Cost
Margin = Profit / Revenue

That is the whole of it. Names are case-insensitive, spaces are ordinary characters, and a reference to another series is read at the same coordinate the formula is being evaluated at — so Revenue - Total Cost means this month's revenue less this month's cost, in every month, without your writing anything to say so.

A series whose name carries punctuation an ordinary name cannot hold is written in square brackets — [Profit Contribution, %]. That spelling is explained once, under Names on the Dimensional expressions page, and it is the same rule for series names and element names alike.

Filtering a reference

A square bracket after a name reads that series somewhere other than here.

Revenue[Italy]              Italy's revenue, whatever coordinate this formula is at
Revenue[Month[-1]]          last month's revenue
Revenue[~]                  the total, across the whole dimension

What goes inside the bracket is a dimensional expression, and everything that page describes is available here — names, paths, positions, offsets, ranges, set operations.

Several dimensions at once

Separate them with commas. Each item filters one dimension.

Revenue[Italy, 2025]              Italy in 2025
Revenue[Europe/_, Month[-1]]      Europe's countries, last month

Order does not matter, because each item names the dimension it applies to by naming something in it. What you cannot do is put two dimensions inside one item: Revenue[2025 | Italy] is an error, because a union of a year and a country is not a set of anything. Split it into two items.

Consecutive brackets mean the same as one bracket with commas, so Revenue[Italy][2025] and Revenue[Italy, 2025] are the same expression. Use whichever reads better.

Everything except the ones you named

A bare * or _ as a filter item stands for every dimension this reference has that I have not already filtered.

Revenue[_]                  every leaf, in every dimension
Revenue[Italy, _]           Italy, and every leaf of every other dimension

* takes each remaining dimension's top elements; _ takes its leaves. The two are opposites, so a single bracket cannot hold both.

Filtering an expression

A bracket can follow a parenthesised expression or a function call, not only a name.

(Revenue - Cost)[2026]      2026's revenue less 2026's cost
SUM(Revenue)[Europe]        the sum, taken in Europe

The filter distributes over everything inside, so (Revenue - Cost)[2026] is exactly Revenue[2026] - Cost[2026]. Write whichever is clearer; on a long expression the first form usually is.

A bracket binds tighter than any operator, so (A - B)[2026] * C filters the subtraction and leaves C alone. If you want the filter over the whole thing, bracket the whole thing.

Arithmetic and comparison

The usual operators, in the usual order:

^                    raise to a power
* /                  multiply, divide
+ -                  add, subtract
= <> < > <= >=       compare
NOT                  negate a condition
AND                  both
OR                   either

^ groups from the right, everything else from the left, and parentheses override all of it. Comparisons do not chain: write A > 1 AND A < 10 rather than 1 < A < 10.

Reducing many values to one

A filter that selects several elements gives several values, and arithmetic has nothing to work on until you say how to reduce them.

SUM(Revenue[Month[$1:$3]])        the first quarter's revenue
AVG(Cost[Europe/_])               the average across Europe's countries
MAX(Revenue[Product])             the best-selling product's revenue
COUNT(Revenue[Product])           how many products have a value

Here Product is a layer. A dimension's own name is not a filter: write Product/_ for its leaves, or Product/* for its top-level elements.

This is how every sum over a dimension is written. There is no separate family of dimensional reduction functions: you select what you want with a filter, and wrap it in the function that reduces it.

The full list of what is available, with arguments, is on the Supported functions page.

Conditions inside a filter

Two keywords narrow a filter by a condition rather than by name. They differ in what they iterate, and that difference decides which one you want.

WHERE — drop elements from one dimension

WHERE attaches to one filter item and tests each candidate element of that dimension. Elements failing the test are removed from the result.

SUM(Revenue[Product WHERE Margin > 0.1])

Products whose margin is at or below 10% are not in the sum at all. The result is narrower, not masked.

INCLUDE — mask cells across the whole result

INCLUDE sits last in the filter list, after a comma, and tests each cell of the result the filters produced. Cells failing the test are left empty; the shape is unchanged.

SUM(Revenue[Product, Month[$1:$3], INCLUDE Margin > 0.1])

Every product-month combination is still there — the ones that fail are simply empty, and SUM adds nothing for them. Because COUNT counts the cells that have a figure, pairing the two is how you ask how much, and in how many cases.

An empty cell here is a hole in the answer this expression produced, not a gap in your model: the model still holds whatever it held at that coordinate, and another expression will read it.

On a single dimension the two forms give the same number, and WHERE is the one to write.

Missing values and errors

Three things can happen where a formula has nothing ordinary to work with, and they are not the same.

An empty cell. A coordinate nobody filled holds nothing. In a sum it gives way: Revenue + OtherRevenue is Revenue where OtherRevenue is empty. In a product or a power it stays empty: Revenue * Price is empty where Price is. Functions and comparisons read it as zero, and AVG and COUNT skip it.

A reference past either end of a layer. In the first month, Revenue[Month[-1]] has nothing to read, and in the last, neither does Revenue[Month[1]]. That coordinate is not in range, and it behaves exactly like an empty cell; COUNT never counts it. So a roll-forward written as Balance[Month[-1]] + NetCashFlow runs from the first period without a guard. A sequential dimension may declare a boundary element before its first period or after its last; where one exists, it is an ordinary element of that layer, and a reference that steps onto it reads its value rather than nothing.

Division by zero, or by an empty cell. Dividing a value by zero, by an empty cell or by a coordinate that is not in range is an error. The cell holds an error and shows one, and it spreads: anything calculated from it is an error too. IFERROR(expression, fallback) supplies a fallback where you want one, and ISERROR tests for it. Only an empty cell divided by an empty cell stays empty, because there was nothing on either side.

They meet in one place worth knowing. A growth calculation divides by the previous period, and in the first period that reference is not in range, so the division fails:

Revenue / Revenue[Month[-1]] - 1              [error] in the first period

Nz does not help here: it stands in for a cell with nothing in it, and here the division failed. Two things do:

IFERROR(Revenue / Revenue[Month[-1]] - 1, 0)   0 in the first period
IF(Revenue[Month[-1]] <> 0, Revenue / Revenue[Month[-1]] - 1)
                                               no value in the first period

The first says the growth is zero. The second says there is no growth to report yet. Which is right is a modelling question rather than a syntax one. The same holds for year-on-year growth, across the whole first year.

Nz(expression, fallback) is the guard for a cell that is merely empty, and ISEMPTY — the function keeps the older spelling — is how you test for one:

ISEMPTY(Revenue[Month[-1]])                   1 when there is nothing to read

A range that runs past the end of a dimension is a different thing again, and is neither: it is simply shorter. Ask for twelve months in a model holding four and you get four, and SUM adds up the four.

Time patterns

Axmo has no year-to-date function, no same-period-last-year function, and no rolling-total function. They are all written from the notation above, which means they work on whatever your Time dimension actually looks like rather than on the calendar someone assumed.

SUM(Revenue[Year[0]/Month[$1:$Month@Year]])              year to date
SUM(Revenue[Quarter[0]/Month[$1:$Month@Quarter]])        quarter to date
Revenue[Year[-1]/Month[$Month@Year]]                     the same month, last year
SUM(Revenue[Month[-11:0]])                               a rolling twelve months
Revenue / Revenue[Year[-1]/Month[$Month@Year]] - 1       year on year

The year-on-year line divides by the year before, so throughout the first year it is an error; guard it as Missing values and errors shows for the first month.

Note the $ in front of Month@Year, because it is the difference between right and wrong. Month@Year produces a number — 3, in March — and inside a position slot a bare number is an offset. So Month[Month@Year] means three periods forward, and returns a number that looks perfectly reasonable. Month[$Month@Year] means the third month of this scope, which is what these patterns want. The rule is the one on the Dimensional expressions page and it holds everywhere: $ for a position, no $ for an offset.

When a series refers to itself

A closing balance is the opening balance plus the movements, and the opening balance is last period's closing balance. Written directly, that is a series whose formula names itself:

Balance = Balance[Month[-1]] + Net Cash Flow

Axmo supports this, and it is the reason the language exists in the form it does. It also comes with rules about which self-references can be resolved and which cannot, and with a decision to make about where the very first balance comes from.

Those are on the Recurrence rules page.