Report opening and closing balances from a ledger

Most finance reporting comes down to three numbers: the balance at the start of a period, the movements during it, and the balance at the end. A general ledger, a bank account, a loan book, or a work-in-progress (WIP) balance all follow this shape. This is simple when your warehouse already stores daily balance snapshots. It gets harder when your source is a raw ledger of individual transactions, with no pre-computed balance anywhere. You cannot just sum the amounts, because a balance is not additive across time. This guide shows a complete pattern for solving this in Omni. You start with raw transactions and finish with self-service opening and closing balances that stay correct no matter which dimensions a user picks, or in what order.

Where this comes up

The trigger is always the same. Your data records individual movements, but the question people ask is about a position at a point in time. You will hit this pattern whenever someone asks for a “balance,” a “position,” or “where we stood” as of a date, and the underlying table only holds transactions:

Finance closes the month and needs opening and closing cash per entity, with the movements reconciling between them.
A controller wants WIP or accrued revenue at the start and end of a period, sliced by client, service line, or owner.
A lending team reports loan book balance by product and vintage, built from drawdowns, repayments, and write-offs.
An ops lead wants stock on hand by warehouse, built from purchase orders, sales, and adjustments.
A RevOps team reports ARR or MRR at period start and end, built from subscription change events.

In every case the source is a movement log, and the requirement is a cumulative position that has to roll up cleanly across any combination of dimensions.

The same pattern across domains

Domain	Balance metric	Transaction source	Entity key
Finance and treasury	Cash or account balance	Payments, receipts, transfers	`account_id`
Professional services	Work-in-progress (WIP)	Time entries, disbursements, invoicing	`engagement_id`
Manufacturing and construction	Work-in-progress (WIP)	Material, labour, and overhead postings	`job_id`
SaaS and subscriptions	ARR or MRR	New, renewal, churn, expansion events	`subscription_id`
Lending and credit	Loan book balance	Drawdowns, repayments, write-offs	`loan_id`
Inventory and supply chain	Stock on hand	Purchase orders, sales, adjustments	`sku_id` + `warehouse_id`
Insurance	Claims reserves	New claims, payments, re-estimates	`claim_id`
HR and workforce	Active headcount	Hires, terminations, transfers	`employee_id`

The entity key is the thing that accumulates a balance. Every other column is just an attribute of it.

Work-in-progress shows up in two different industries, and both fit this pattern. In professional services such as accounting, legal, and consulting, WIP is unbilled time and disbursements: work that has been delivered but not yet invoiced. In manufacturing and construction, WIP is the cost of partially finished goods or a job mid-build, the materials, labour, and overhead accrued before completion. The inputs differ, but the accounting is the same. Each is a running balance built from a transaction log, so both report opening and closing WIP using the steps below.

The problem: semi-additive measures

Most measures are fully additive. You can sum them across any dimension and get a correct result. Revenue, deposits, hours worked, and order count are all additive. Balances are different. They are semi-additive. You can sum them across dimensions like account, region, or customer, but you cannot sum them across time. A balance of $10,000 on Monday and $10,500 on Tuesday is not $20,500. The balance grew by $500. To report a balance you have to do two things in order:

Pick a single point in time, the first or last date in the period.
Then sum across the other dimensions.

This is exactly what omni_dimensionalize is built for.

Requirements

To follow this guide you will need:

A transactional source table with a transaction date, an entity key, the descriptive dimensions you want to slice by, and a signed amount per movement.
A transformation layer (dbt, scheduled SQL, or similar) to build the snapshot table in Step 1.
Connection permissions to edit the shared model, or a development branch to work in.

The three-step pattern

Build a daily snapshot table

Your transformation layer computes a daily snapshot with a running balance per entity. The key design decision is the partition key.Partition by the entity key, not by the descriptive dimensions. The account_id (or loan_id, wip_id, and so on) is the thing that accumulates balance. Customer, region, and account type are attributes of that entity. Partitioning by the entity key is simpler, faster, and correct. If an attribute changes, for example an account moves to a new region, the running balance still tracks against the entity.Materialize this in the transformation layer, do not compute it live. Build the snapshot once as a table in dbt or a scheduled job and let Omni query the result. A window function that scans the full ledger is expensive to re-run on every query, and materializing it keeps the running balance deterministic and fast. Refresh it on the same cadence as your reporting, which is daily for most balance use cases.Snapshot at the lowest grain that accumulates a balance, which may be finer than the account. Here, one row per account_id per day is enough. In other domains the entity sits below the account: inventory is per sku_id + warehouse_id, WIP is per job_id or engagement_id, and a multi-currency account is per account_id + currency. Build the snapshot at that grain. Omni rolls it up to whatever a user groups by, but it can never recover detail the snapshot has already aggregated away.Take a ledger of account transactions:

date	account_id	region	account_type	customer	amount
2025-01-01	ACC-1001	APAC	Savings	Acme Corp	$5,000
2025-01-01	ACC-1002	EMEA	Current	Globex Inc	$12,000
2025-01-01	ACC-1003	APAC	Current	Acme Corp	$8,000
2025-01-15	ACC-1001	APAC	Savings	Acme Corp	$2,500
2025-01-15	ACC-1002	EMEA	Current	Globex Inc	-$3,000
2025-02-01	ACC-1001	APAC	Savings	Acme Corp	$1,000
2025-02-01	ACC-1003	APAC	Current	Acme Corp	-$2,000
2025-02-15	ACC-1002	EMEA	Current	Globex Inc	$7,500
2025-03-01	ACC-1001	APAC	Savings	Acme Corp	-$500
2025-03-01	ACC-1003	APAC	Current	Acme Corp	$4,000

A window function turns it into a daily snapshot with a running balance per account_id:

-- account_daily_snapshot: materialize in your transformation layer
-- (e.g. a dbt model, or a scheduled table in Snowflake or Databricks)
with daily_movements as (
    select
        transaction_date as snapshot_date,
        account_id,
        region,
        account_type,
        customer,
        sum(amount) as daily_movement
    from stg_account_transactions
    group by all
)

select
    *,
    sum(daily_movement) over (
        partition by account_id
        order by snapshot_date
        rows between unbounded preceding and current row
    ) as running_balance
from daily_movements

The result accumulates per entity across time:

snapshot_date	account_id	region	account_type	customer	daily_movement	running_balance
2025-01-01	ACC-1001	APAC	Savings	Acme Corp	$5,000	$5,000
2025-01-01	ACC-1002	EMEA	Current	Globex Inc	$12,000	$12,000
2025-01-01	ACC-1003	APAC	Current	Acme Corp	$8,000	$8,000
2025-01-15	ACC-1001	APAC	Savings	Acme Corp	$2,500	$7,500
2025-01-15	ACC-1002	EMEA	Current	Globex Inc	-$3,000	$9,000
2025-02-01	ACC-1001	APAC	Savings	Acme Corp	$1,000	$8,500
2025-02-01	ACC-1003	APAC	Current	Acme Corp	-$2,000	$6,000
2025-02-15	ACC-1002	EMEA	Current	Globex Inc	$7,500	$16,500
2025-03-01	ACC-1001	APAC	Savings	Acme Corp	-$500	$8,000
2025-03-01	ACC-1003	APAC	Current	Acme Corp	$4,000	$10,000

ACC-1001 starts at $5,000, gains $2,500 on Jan 15 to reach $7,500, gains $1,000 on Feb 1 to reach $8,500, and loses $500 on Mar 1 to reach $8,000.

Model the balances with omni_dimensionalize

This is where Omni does the work. omni_dimensionalize converts a measure into a dimension within the current query’s grouping context. Use it to find the first and last date in any filtered period, per group, then filter the running balance to only those dates.

dimensions:
  # First and last date WITHIN each GROUP BY combination
  first_date_in_period:
    hidden: true
    sql: omni_dimensionalize(min(${snapshot_date}))

  last_date_in_period:
    hidden: true
    sql: omni_dimensionalize(max(${snapshot_date}))

  is_opening_date:
    hidden: true
    sql: ${snapshot_date[date]} = ${first_date_in_period[date]}

  is_closing_date:
    hidden: true
    sql: ${snapshot_date[date]} = ${last_date_in_period[date]}

measures:
  # Semi-additive: sum the running balance, but only on the opening date
  opening_balance:
    label: Opening Balance
    sql: ${running_balance}
    aggregate_type: sum
    format: currency_2
    filters:
      is_opening_date:
        is: true

  # Semi-additive: sum the running balance, but only on the closing date
  closing_balance:
    label: Closing Balance
    sql: ${running_balance}
    aggregate_type: sum
    format: currency_2
    filters:
      is_closing_date:
        is: true

  # Additive: movements can be summed freely across time
  period_movement:
    label: Period Movement
    sql: ${daily_movement}
    aggregate_type: sum
    format: currency_2

Here is what happens when a user runs a query:

They pick dimensions, for example Region and Account Type, and filter to a date range such as Q1 2025.
omni_dimensionalize(min(${snapshot_date})) finds the earliest date within each group in the result set.
is_opening_date is true only for rows on that group’s first date.
The opening_balance measure sums running_balance only where is_opening_date is true.
Because the snapshot is at the entity grain, Omni’s sum() rolls up correctly across entities inside each group.

The hierarchy a user selects is just a GROUP BY ordering. As long as the snapshot sits at the lowest grain, one row per entity per day, Omni handles any roll-up automatically.The opening and closing measures are filtered measures. The movement measures need no special handling.

Separate additive from semi-additive measures

This is the distinction to make clear to your users.

Measure	Type	Sum across time?	Sum across dimensions?	Omni approach
Daily movement	Additive	Yes	Yes	Standard `aggregate_type: sum`
Deposits	Additive	Yes	Yes	Standard `aggregate_type: sum`
Withdrawals	Additive	Yes	Yes	Standard `aggregate_type: sum`
Opening balance	Semi-additive	No	Yes	Filtered measure with `omni_dimensionalize`
Closing balance	Semi-additive	No	Yes	Filtered measure with `omni_dimensionalize`

Derived metrics build on both types. Net change is closing_balance - opening_balance. Growth percent is (closing_balance - opening_balance) / opening_balance. Define these as standard measures that reference the others.

Why dimension order does not matter

A common worry is whether the numbers change when a user reorders dimensions. They do not. Here is the proof using the account data above, filtered to Jan 1 to Mar 1 2025. Hierarchy A: Region, then Customer, then Account Type

Region	Customer	Account Type	Opening	Closing	Movement
APAC	Acme Corp	Savings	$5,000	$8,000	$3,000
APAC	Acme Corp	Current	$8,000	$10,000	$2,000
EMEA	Globex Inc	Current	$12,000	$16,500	$4,500

Hierarchy B: Customer, then Account Type, then Region

Customer	Account Type	Region	Opening	Closing	Movement
Acme Corp	Current	APAC	$8,000	$10,000	$2,000
Acme Corp	Savings	APAC	$5,000	$8,000	$3,000
Globex Inc	Current	EMEA	$12,000	$16,500	$4,500

Same numbers, different visual nesting. The GROUP BY contains the same columns, so Omni generates identical SQL. Rolled up to Region only

Region	Opening	Closing	Movement
APAC	$13,000	$18,000	$5,000
EMEA	$12,000	$16,500	$4,500
Grand total	$25,000	$34,500	$9,500

At the Region level, omni_dimensionalize finds the min and max date across all accounts in each region. APAC opening is ACC-1001 ($5,000) plus ACC-1003 ($8,000) on Jan 1, which is $13,000. APAC closing is ACC-1001 ($8,000) plus ACC-1003 ($10,000) on Mar 1, which is $18,000. The balances aggregate correctly at every level, because the snapshot is at the entity grain and omni_dimensionalize adapts to whatever the current GROUP BY is.

Variations and advanced patterns

Reconciliation: closing equals next period’s opening

If users need to confirm that the closing balance for one period equals the opening balance for the next, build a reconciliation view that materializes both values side by side, per period and per dimension combination.

All-time balance alongside a filtered period

To show an all-time balance next to the filtered period’s opening and closing, use cancel_query_filters on a level of detail field:

dimensions:
  all_time_balance:
    sql: ${running_balance}
    group_by:
      aggregate_type: sum
      fixed: [ account_id ]
      cancel_query_filters: true

When you already have snapshot data

If your warehouse already stores daily balance snapshots, skip Step 1 and go straight to the omni_dimensionalize pattern in Step 2.

Next steps

Validate the model against a known reconciliation period before you publish it.
Build a workbook with field selectors so users can switch dimensions on the fly.
Read more on semi-additive measures and omni_dimensionalize.
Review filtered measures and level of detail fields for the supporting concepts.