Multi-currency fact chain

Transaction: original amount and currency

Ledger: functional amount and event-date rate

Reporting: target currency and policy

Period end: remeasurement adjustment

Governance: source, direction, and version

An amount is not a decimal without currency context

One hundred dollars, euros, and yen cannot be summed. A fact must preserve transaction_amount and transaction_currency, then store functional or reporting amounts only under an explicit approved policy.

This is analytical modeling guidance, not accounting advice. Transaction, settlement, closing, and planning views can require different rates, while monetary and non-monetary treatment depends on applicable standards and company policy. A tool must not invent that judgment.

Declare functional and reporting currency by entity and period

A group may contain entities with different functional currencies and a separate management reporting currency. Model entity, ledger, functional_currency, reporting_currency, effective period, and policy_version instead of hard-coding a group-wide base currency.

Legal reporting, sales operations, cash settlement, and budget comparison are distinct uses. Each metric must name its amount column, rate type, date role, and policy version. A generic Sales label hides material semantic differences.

Retain original and converted amounts in the fact

An order line can carry transaction, functional, and reporting amounts and currencies, plus the rate ID behind each conversion. Original currency is required for audit and controlled restatement; stored converted values preserve the decision made at posting time.

Discount, tax, freight, refund, and cost need compatible grain, sign, and currency treatment. Never sum mixed currencies and multiply by an average rate, and never duplicate an order-header amount across lines.

Make quote direction and valid time unambiguous

An FX table should contain from_currency, to_currency, rate_type, rate, valid_from, valid_to, source, publication time, ingestion time, and version. State that one unit of from equals rate units of to; a pair label alone invites inversion errors.

Define weekend, holiday, and missing-date fallback. Intraday rates require timestamps and timezone. Preserve competing source values and select by approved priority. Missing rates must not silently become one or an arbitrary previous record.

Separate event, settlement, closing, and average rates

Transaction recognition follows event_date, cash settlement follows settlement_date, and period-end reporting may use a closing rate. Approved average or planning rates are separate analytical policies. Model date roles and rate_type in the join.

A transaction-weighted average is not automatically a market period average. If only monthly rates exist, disclose monthly resolution rather than materializing identical daily records and claiming daily precision.

Govern triangulation, inverses, precision, and rounding

When a direct pair is absent, an approved pivot may support triangulation. Retain both rate IDs, path, and final precision. Forward and reverse vendor rates may not be exact inverses because of spreads and publication timing, so preserve evidence.

Define amount scale, rate scale, rounding mode, and whether rounding occurs per line or after aggregation. Record deterministic residual adjustments rather than hiding the difference in an arbitrary business row.

Represent remeasurement and translation as adjustment facts

Do not overwrite the original functional amount when an open item is remeasured or settled. An adjustment fact can store object, period, old and new amount, difference, rate, policy, run, and reversal relationship.

This permits both transaction-time operating analysis and period-end reporting. In-place updates destroy the explanation of whether a change came from business activity, FX movement, or a new policy.

Treat backfills and policy changes as controlled restatement

When a rate source publishes late or revises history, create a new version and identify affected facts. Finance policy determines whether to reopen, post a current adjustment, or leave a closed period unchanged.

Reports should not always join to the latest rate because published numbers would drift without a restatement marker. Model close status, approval, impact, and replay evidence.

Accept with currency conservation and edge cases

Require currency on every amount, a unique valid rate, reproducible conversion, consistent refund signs, no mixed-currency totals, and conserved adjustments. Test direct and triangulated pairs, inversion, weekends, negative values, rare currencies, missing rates, rounding, and repeated backfill.

Reconcile functional totals by entity and period to an approved ledger or report, separating business, rate, rounding, and timing differences. A few ordinary USD orders do not exercise the risky paths.

BuildTable.ai boundary

Have finance approve roles and policies, inventory every amount and date, build versioned FX facts, preserve original amounts, implement adjustments, and reconcile before publishing semantic metrics. Monitor missing rates, stale rates, restatement impact, and ledger difference.

BuildTable.ai may be evaluated for fact, rate, and semantic result modeling. This article does not establish built-in IAS 21 treatment, authoritative rate acquisition, or accounting judgment. Sources, precision, scheduling, approval, and restatement behavior require project verification by qualified owners.

Why do cross-border totals drift? Model transaction, functional, and reporting currency separately