ACAW: A Running Total That Resets and Skips Nulls

A Running Total That Resets and Skips Nulls

An analyst has transaction data sorted by account_id then txn_date, with a amount field that contains nulls (about 3% of rows) and occasional negative adjustments. They need a running balance per account that: (1) resets to zero at each new account, (2) treats null amounts as zero for the running total but preserves the null in the output, and (3) produces a final balance per account for a summary. Their first attempt used a Multi-Row Formula with [Row-1:RunningBalance] + [amount], and the results were wrong at account boundaries and when the first row of an account had a null amount.

1. Explain why the Multi-Row Formula gave wrong results at account boundaries and with leading nulls, and give a corrected expression.

Read the worked answer

The Multi-Row Formula [Row-1:RunningBalance] + [amount] has two failure modes.

Account boundaries: Multi-Row Formula does not know about groups. When the account changes, [Row-1:RunningBalance] still holds the previous account's last balance, so the new account starts from the old total instead of zero. The formula must detect the boundary and reset.

Leading nulls: [Row-1:RunningBalance] + [amount] where amount is null yields null (null propagates through arithmetic), so a null on the first row of an account poisons the entire running total for that account-every subsequent row inherits null.

Corrected expression (Multi-Row Formula, values for rows that don't exist = 0 / empty):

IF IsNull([Row-1:account_id]) OR [account_id] != [Row-1:account_id]
THEN IIF(IsNull([amount]), 0, [amount])
ELSE [Row-1:RunningBalance] + IIF(IsNull([amount]), 0, [amount])
ENDIF

Key points: the reset branch checks the group field, not the running value; IIF(IsNull([amount]),0,[amount]) substitutes zero only for the arithmetic while leaving the original amount column untouched (so the null is preserved in output). The row must be sorted by account then date before this tool, or the boundary logic is meaningless.

2. How would you produce the per-account final balance for the summary, and what pitfall should you avoid?

Read the worked answer

Two clean options:

Summarize after the running total: Since the running balance is monotonic within each account (each row carries the cumulative value), the last row per account holds the final balance. Use a Summarize grouped by account_id taking Max of RunningBalance-valid only because the balance is cumulative and the last value is the largest in normal cases. This breaks if large negative adjustments make an earlier row exceed the final one, so Max is unsafe here.
Summarize the raw amounts directly: Sum(IIF(IsNull([amount]),0,[amount])) grouped by account_id. This is the robust answer-it doesn't depend on row order or on the running total being monotonic, and nulls are handled explicitly.

Pitfall to avoid: Using Max/Min of the running balance to stand in for "last value." It silently returns the wrong number whenever negative adjustments make the peak occur before the end. If you truly need the last row's value, use a Sample (Last N=1 per group) or a Sort + Sample rather than an aggregate that assumes monotonicity. Also avoid summing the already-cumulative column-that double-counts.