Effective Balances

Standard balances answer the question "what is this account worth right now?". They are a single running total that always reflects the latest state of the ledger.

Effective balances answer a different question: "what was true as of, or during, a particular period?" — for example "what was this account's balance at the end of January?" or "how much did merchant M001 deposit in Q1?".

Twisp builds effective balances by rolling up a calculation's dimensions by the effective date of each entry into year, month, and day buckets. Because every entry carries an effective date, these rollups are maintained on write, so you can query a historical or period-scoped balance with the same certainty and latency as a live balance.

How effective balances work

Effective balances are an extension of calculations — the same compute-on-write rollups that produce dimensional balances. When you set enableEffectiveBalances: true on a calculation, Twisp creates three child calculations behind your parent calculation, one for each granularity:

The effective date components (year, month, day) are prepended to your custom dimensions. So a calculation that already dimensions by merchant_id and category will, with effective balances enabled, maintain buckets keyed by (year, merchant_id, category), (year, month, merchant_id, category), and (year, month, day, merchant_id, category).

At query time you supply the parent calculationId and an effective argument; Twisp resolves the correct child calculation from the period format and merges the effective-date dimensions with the custom dimensions you pass.

Setting up an effective calculation

Enable effective balances on a calculation by setting config.enableEffectiveBalances. Custom dimensions are defined as usual with CEL expressions over the entry:

Copymutation CreateEffectiveCalculation {
  createCalculation(
    input: {
      calculationId: "22222222-2222-2222-2222-222222222222"
      code: "merchant_balances"
      description: "Balances by merchant with effective date rollups"
      dimensions: [
        {
          alias: "merchant_id"
          value: "string(context.vars.entry.metadata.?merchant_id.orValue(''))"
        }
        { alias: "category", value: "string(context.vars.entry.metadata.?category.orValue(''))" }
      ]
      condition: "context.vars.entry.metadata != null && ('merchant_id' in context.vars.entry.metadata) && ('category' in context.vars.entry.metadata)"
      scope: GLOBAL
      config: { enableEffectiveBalances: true }
    }
  ) {
    calculationId
    code
    config {
      enableEffectiveBalances
    }
    dimensions {
      alias
      value
    }
  }
}

You can also enable effective balances on a calculation with no custom dimensions — the child calculations are then keyed only by the effective-date components, giving you plain time-bucketed balances for the account.

Once the calculation exists, post your normal dated activity. The examples below assume deposits to a single account across three months, tagged with merchant and category metadata:

Querying a single period

Pass effective.period to read the balance for one period. Twisp infers the granularity from the format — YYYY for a year, YYYY-MM for a month, YYYY-MM-DD for a day — and selects the matching child calculation. The dimensions field in the response echoes back the resolved bucket, including the effective-date components.

Copyquery QueryEffectiveBalancePeriod {
  year: balance(
    accountId: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "22222222-2222-2222-2222-222222222222"
    dimension: { merchant_id: "M001", category: "FOOD" }
    effective: { period: "2024" }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    calculationId
    dimensions
  }

  month: balance(
    accountId: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "22222222-2222-2222-2222-222222222222"
    dimension: { merchant_id: "M001", category: "FOOD" }
    effective: { period: "2024-02" }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    calculationId
    dimensions
  }

  day: balance(
    accountId: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "22222222-2222-2222-2222-222222222222"
    dimension: { merchant_id: "M001", category: "FOOD" }
    effective: { period: "2024-02-15" }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    calculationId
    dimensions
  }
}

The year bucket totals all three FOOD deposits (6000.00); the month and day buckets isolate February's activity (2000.00). Note how the resolved calculationId differs per granularity — these are the child calculations — and how dimensions reports the effective-date components:

Copy{
  "data": {
    "year": {
      "available": { "crBalance": { "units": "6000.00" } },
      "calculationId": "25c6d76d-9e22-5726-8e13-53bfe3f60e88",
      "dimensions": { "category": "FOOD", "merchant_id": "M001", "year": 2024 }
    },
    "month": {
      "available": { "crBalance": { "units": "2000.00" } },
      "calculationId": "61d544eb-7a1a-5fdc-8494-a89b795aab75",
      "dimensions": { "category": "FOOD", "merchant_id": "M001", "month": 2, "year": 2024 }
    },
    "day": {
      "available": { "crBalance": { "units": "2000.00" } },
      "calculationId": "8b01e31e-5df4-5e4e-a68c-1f3e0f1db826",
      "dimensions": {
        "category": "FOOD",
        "day": 15,
        "merchant_id": "M001",
        "month": 2,
        "year": 2024
      }
    }
  }
}

Aggregating a range of periods

effective.range sums activity across a span of periods given a closed gte/lte pair. This is convenient for income-statement-style totals over a window.

Copyquery QueryEffectiveBalanceRange {
  range: balance(
    accountId: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "22222222-2222-2222-2222-222222222222"
    dimension: { merchant_id: "M001", category: "FOOD" }
    effective: { range: { gte: "2024-01", lte: "2024-03" } }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    calculationId
    dimensions
  }
}

Copy{
  "data": {
    "range": {
      "available": { "crBalance": { "units": "3000.00" } },
      "calculationId": "22222222-2222-2222-2222-222222222222",
      "dimensions": { "category": "FOOD", "merchant_id": "M001" }
    }
  }
}

Cumulative "as-of" balances

effective.cumulative returns the balance accumulated from the beginning of the ledger through the given date — the answer to "what was the balance as of this date?".

Copyquery QueryEffectiveBalanceCumulative {
  cumulative: balance(
    accountId: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "22222222-2222-2222-2222-222222222222"
    dimension: { merchant_id: "M001", category: "FOOD" }
    effective: { cumulative: "2024-03-31" }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    calculationId
    dimensions
  }
}

Through March 31st, all three FOOD deposits are included:

Copy{
  "data": {
    "cumulative": {
      "available": { "crBalance": { "units": "6000.00" } },
      "calculationId": "22222222-2222-2222-2222-222222222222",
      "dimensions": { "category": "FOOD", "merchant_id": "M001" }
    }
  }
}

Per-period and running balances

effective.periods enumerates every period in the half-open interval [gte, lt) and returns one entry per period in the effectiveBalances array. Granularity is inferred from the bound format, both bounds must use the same format, and lt must be strictly greater than gte.

The accumulate flag controls what each entry — and the top-level balance — represents:

• accumulate: false (default): each effectiveBalances entry is that period's own activity, and the top-level balance is the sum across the range. Useful for income statements and per-period breakdowns.
• accumulate: true: each entry is the running cumulative balance through the end of its period, and the top-level balance is the closing cumulative just before lt. Useful for daily statement snapshots and "as-of" series.

Copyquery QueryEffectiveBalancePeriods {
  periods: balance(
    accountId: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "22222222-2222-2222-2222-222222222222"
    dimension: { merchant_id: "M001", category: "FOOD" }
    effective: { periods: { gte: "2024-01", lt: "2024-04", accumulate: true } }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    calculationId
    dimensions
    effectiveBalances {
      effective
      dimensions
      available(layer: SETTLED) {
        crBalance {
          units
        }
      }
    }
  }
}

With accumulate: true, each monthly entry carries the running total — January 1000.00, February 3000.00, March 6000.00 — and the top-level balance is the closing cumulative (6000.00):

Copy{
  "data": {
    "periods": {
      "available": { "crBalance": { "units": "6000.00" } },
      "calculationId": "22222222-2222-2222-2222-222222222222",
      "dimensions": { "category": "FOOD", "merchant_id": "M001" },
      "effectiveBalances": [
        {
          "effective": "2024-01",
          "dimensions": { "category": "FOOD", "merchant_id": "M001", "year": "2024", "month": "1" },
          "available": { "crBalance": { "units": "1000.00" } }
        },
        {
          "effective": "2024-02",
          "dimensions": { "category": "FOOD", "merchant_id": "M001", "year": "2024", "month": "2" },
          "available": { "crBalance": { "units": "3000.00" } }
        },
        {
          "effective": "2024-03",
          "dimensions": { "category": "FOOD", "merchant_id": "M001", "year": "2024", "month": "3" },
          "available": { "crBalance": { "units": "6000.00" } }
        }
      ]
    }
  }
}

Run the same query with accumulate: false to instead get each month's standalone activity (1000.00, 2000.00, 3000.00) with a top-level total of 6000.00. The opening balance for a range can be recovered by querying the prior period with cumulative, or by subtracting the first entry's activity from its accumulated value.

Controlling the effective date with effectiveDateSource

By default, the effective-date buckets are derived from the transaction's effective date (context.vars.transaction.effective). Sometimes you want the rollup to follow a different date — most commonly a statement or posting date that differs from when the activity actually occurred.

Set config.effectiveDateSource to a CEL expression resolving to a date. This is what makes effective balances robust to backdated and late-arriving activity: you decide which date the balance "belongs" to. A common pattern is to read a date from entry metadata and fall back to the transaction effective date when it is absent:

Copyconfig: {
  enableEffectiveBalances: true
  effectiveDateSource: "date(context.vars.entry.?metadata.?statementDate.orValue(context.vars.transaction.effective))"
}

For example, suppose two deposits occur on 2024-01-15 and 2024-02-20, but both belong to a statement dated 2024-06-01 (carried on each entry as a statementDate metadata value):

With effectiveDateSource pointed at the statement date, the rollup buckets use June 2024, not the transaction effective dates. Querying the statement period returns the full 1500.00:

Copyquery QueryStatementDateBalances {
  month: balance(
    accountId: "bb001111-2222-3333-4444-555566667777"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "aaaa1111-bbbb-cccc-dddd-eeee2222ffff"
    dimension: {}
    effective: { period: "2024-06" }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
    dimensions
  }
}

Copy{
  "data": {
    "month": {
      "available": { "crBalance": { "units": "1500.00" } },
      "dimensions": { "month": 6, "year": 2024 }
    }
  }
}

Querying the original transaction effective period (2024-01) now returns null — no buckets were created there, because effectiveDateSource redirected every entry to June:

Copyquery QueryTransactionEffectiveEmpty {
  jan: balance(
    accountId: "bb001111-2222-3333-4444-555566667777"
    journalId: "11111111-1111-1111-1111-111111111111"
    calculationId: "aaaa1111-bbbb-cccc-dddd-eeee2222ffff"
    dimension: {}
    effective: { period: "2024-01" }
  ) {
    available(layer: SETTLED) {
      crBalance {
        units
      }
    }
  }
}

Copy{
  "data": {
    "jan": null
  }
}

Practical applications

Effective balances are the building block for any reporting that is anchored to when activity is effective rather than when it was recorded:

• Backdated and late-arriving transactions. A correction posted today with an effective date of last month lands in last month's bucket automatically. The historical period balance updates on write — no re-run or batch job — so reports stay correct even as adjustments trickle in.
• Monthly statements. Use effectiveDateSource to bucket activity by statement date, then read the statement period with effective.period, or use effective.periods with accumulate: true to produce a running daily balance for the statement.
• Income statements. Sum a window of activity with effective.range, or break it out period-by-period with effective.periods and accumulate: false.
• As-of / point-in-time balances. effective.cumulative answers "what was the balance as of date X" for audits, reconciliations, and regulatory snapshots.
• Dimensional time series. Combine effective dates with your own dimensions (merchant, category, product) to get per-period totals sliced any way your calculation defines.