> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omni.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Report opening and closing balances from a ledger

> Turn a transactional ledger into opening, closing, and movement balances that stay correct across any dimension, using a daily snapshot and omni_dimensionalize.

export const categoryIcons = {
  'administration': 'lock',
  'api': 'terminal',
  'connections': 'database',
  'dashboards': 'table-columns',
  'embed': 'code',
  'errors': 'exclamation',
  'modeling': 'wrench',
  'patterns': 'plus',
  'schedules & alerts': 'envelope',
  'visualizations': 'chart-column',
  'workbooks': 'book'
};

export const GuideSidebar = ({category, relatedLinks, updatedDate}) => {
  const [progress, setProgress] = React.useState(0);
  React.useEffect(() => {
    const sidebar = document.querySelector('.guide-sidebar');
    if (!sidebar) return;
    let container = sidebar.parentElement;
    while (container && !container.querySelector('.guide-header')) {
      container = container.parentElement;
    }
    if (container && !container.classList.contains('guide-page-layout')) {
      container.classList.add('guide-page-layout');
    }
  }, []);
  React.useEffect(() => {
    const handleScroll = () => {
      const scrollTop = window.scrollY;
      const docHeight = document.documentElement.scrollHeight - window.innerHeight;
      const scrollPercent = docHeight > 0 ? scrollTop / docHeight * 100 : 0;
      setProgress(Math.min(100, Math.max(0, scrollPercent)));
    };
    window.addEventListener('scroll', handleScroll, {
      passive: true
    });
    handleScroll();
    return () => window.removeEventListener('scroll', handleScroll);
  }, []);
  const icon = category ? categoryIcons[category.toLowerCase()] || 'book' : 'book';
  return <aside className="guide-sidebar">
      <div className="guide-sidebar-content">
        <a href="/guides" className="guide-sidebar-back">
          <Icon icon="arrow-left" iconType="solid" size={14} />
          <span>All guides</span>
        </a>

        <div className="guide-sidebar-section">
          <div className="guide-sidebar-label">Progress</div>
          <div className="guide-sidebar-progress">
            <div className="guide-mascot">
              <svg viewBox="0 0 688 690" width="48" height="48">
                <defs>
                  <clipPath id="progressClip">
                    <rect x="0" y={0} width="688" height={progress * 6.9} />
                  </clipPath>
                </defs>

                {}
                <path d="M343.67 1.5C542.684 1.5 685.84 149.351 685.84 344.84C685.84 540.328 542.685 688.18 343.67 688.18C144.655 688.18 1.5 540.318 1.5 344.84C1.50007 149.361 144.655 1.50005 343.67 1.5Z" fill="#FCFCF7" stroke="#FF5FA2" strokeWidth="3" />

                {}
                <path d="M343.67 0C143.81 0 0 148.55 0 344.84C0 541.13 143.81 689.68 343.67 689.68C543.53 689.68 687.34 541.14 687.34 344.84C687.34 148.54 543.53 0 343.67 0Z" fill="#FF5FA2" clipPath="url(#progressClip)" />

                {}
                <path d="M337.89 319.29C337.89 336.75 322.49 350.14 302.81 349.83C286.18 349.57 273.89 337.29 274.37 321.45C274.88 304.82 290.91 290.88 309.98 290.44C325.69 290.09 337.88 302.69 337.88 319.29H337.89Z" fill="#4D122C" />
                <path d="M566.17 319.29C566.17 336.75 550.77 350.14 531.09 349.83C514.46 349.57 502.17 337.29 502.65 321.45C503.16 304.82 519.19 290.88 538.26 290.44C553.97 290.09 566.16 302.69 566.16 319.29H566.17Z" fill="#4D122C" />
                <path d="M367.74 342.07C360.22 346.32 359.4 354.9 370.62 366.4C381.85 377.9 399.76 389.56 420.81 389.18C441.88 389.1 460.67 377.72 472.47 363.53C473.83 361.93 478.84 356.88 478.51 351.07C478.32 348.35 476.17 341.19 467.83 341.38C463.46 341.44 461.21 343.68 456.69 347.36C445.2 356.14 432.7 361.21 420.56 361.27C408.43 361.43 395.68 356.17 385.39 347.22C380.32 342.81 375.25 337.82 367.74 342.07Z" fill="#4D122C" />
              </svg>
            </div>
            <span className="guide-sidebar-progress-text">{Math.round(progress)}%</span>
          </div>
        </div>

        {category && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Category</div>
            <div className="guide-sidebar-category">
              <Icon icon={icon} iconType="solid" size={14} />
              <span>{category}</span>
            </div>
          </div>}

        {updatedDate && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Last updated</div>
            <div className="guide-sidebar-date">{updatedDate}</div>
          </div>}

        {relatedLinks && relatedLinks.length > 0 && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Related</div>
            <ul className="guide-sidebar-links">
              {relatedLinks.map((link, index) => <li key={index}>
                  <a href={link.href}>{link.title}</a>
                </li>)}
            </ul>
          </div>}
      </div>
    </aside>;
};

export const GuideTitle = ({title}) => {
  return <div className="guide-header">
      <h1 className="guide-title">{title}</h1>
    </div>;
};

<GuideSidebar
  categoryIcons={categoryIcons}
  category="patterns"
  updatedDate="June 2026"
  relatedLinks={[
{ title: "Semi-additive measures", href: "/modeling/measures#semi-additive-measures-with-omni_dimensionalize" },
{ title: "Filtered measures", href: "/analyze-explore/custom-fields/filtered-measures" },
{ title: "Level of detail", href: "/modeling/dimensions/parameters/level-of-detail" },
{ title: "Table visualizations", href: "/visualize-present/visualizations/types/table" }
]}
/>

<GuideTitle title="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.

<Note>
  **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.
</Note>

## 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:

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

This is exactly what [`omni_dimensionalize`](/modeling/measures#semi-additive-measures-with-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

<Steps>
  <Step titleSize="h2" title="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`:

    ```sql theme={null}
    -- 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.
  </Step>

  <Step titleSize="h2" title="Model the balances with omni_dimensionalize">
    This is where Omni does the work. [`omni_dimensionalize`](/modeling/measures#semi-additive-measures-with-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.

    ```yaml theme={null}
    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:

    1. They pick dimensions, for example Region and Account Type, and filter to a date range such as Q1 2025.
    2. `omni_dimensionalize(min(${snapshot_date}))` finds the earliest date within each group in the result set.
    3. `is_opening_date` is true only for rows on that group's first date.
    4. The `opening_balance` measure sums `running_balance` only where `is_opening_date` is true.
    5. 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](/analyze-explore/custom-fields/filtered-measures). The movement measures need no special handling.
  </Step>

  <Step titleSize="h2" title="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.
  </Step>
</Steps>

## 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`](/modeling/filters/operators/cancel-query-filter) on a [level of detail](/modeling/dimensions/parameters/level-of-detail) field:

```yaml theme={null}
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`](/modeling/measures#semi-additive-measures-with-omni_dimensionalize).
* Review [filtered measures](/analyze-explore/custom-fields/filtered-measures) and [level of detail fields](/modeling/dimensions/parameters/level-of-detail) for the supporting concepts.