← Back to the journal

DataFocus · Journal

Why Finance and Sales Report Different Revenue: Five Choices Hidden Inside Every Metric

Two reports disagree on one metric because each re-implements it and makes five unlabeled choices: filters, date column, grain, join path and source system. A worked revenue reconciliation, a triage order and what an executable, owned definition requires.

Two reports disagree on "revenue" because neither one uses the revenue metric; each re-implements it, and every implementation makes five choices the label does not show: which rows are filtered out, which date column places a transaction in a period, what one row represents, which path joins the tables, and which system is treated as authoritative. Each report is internally consistent. They are answering different questions under the same name. The disagreement ends only when the definition exists exactly once, in executable form with a named owner, and every consumer, whether a dashboard, a spreadsheet or an AI agent, calls that definition instead of rewriting it.

Which five choices make the same metric diverge?

Lay the two queries side by side and the gap decomposes into some combination of these. The last column is the question that exposes each one in under a minute.

ChoiceFinance's revenueSales' revenueEffect on the numberQuestion that exposes it
FiltersExcludes cancelled orders, credit notes, intercompany salesEverything the team bookedSales runs higher"Which status and record-type filters are in the WHERE clause?"
Time basisRecognition or invoice dateOrder or signature dateSame annual total, different quarters"Which date column is in the GROUP BY?"
GrainOne row per invoice lineOne row per opportunity, or per order joined to shipmentsMulti-shipment orders counted several times; partial invoices dropped"What does one row represent before the SUM?"
Join pathInvoice to customer master in the ERPOpportunity to account in the CRMA customer split in two, or two merged into one"Which key links the fact to the customer?"
Source systemGeneral ledgerPipelineDifferent records altogether"Which system did the rows come from?"

None of these is a mistake on its own. A sales leader is right to care about bookings on the day a contract is signed; a controller is right to care about revenue on the day it can be recognized. The failure is upstream of both teams: two figures reach the board slide labeled "Revenue", and nothing on the slide shows which five choices sit behind each one.

Why does the time basis start the most arguments?

A single commercial record carries several dates: ordered, shipped, invoiced, paid, recognized. Choose a different one and a deal signed on 30 June lands in Q2 for Sales and Q3 for Finance. Both teams can agree on the annual total and still fight about every quarter, because the divergence is invisible in the output: two correct quarterly totals look exactly like one correct and one wrong.

A second, quieter time problem sits inside the definition itself: the gap between when a rule takes effect and when it is approved. Suppose "net revenue" is redefined to exclude a new class of rebates, effective 1 October, and signed off on 20 October. Reports built in the nineteen days between may use either version. Worse, a report re-run in December may recompute October under the new rule, so the October board pack no longer matches today's query for the same month, and nobody changed any data.

DataFocus's ontology documentation treats these as two separate timestamps. Business-effective time is distinct from approval time; an unapproved rule cannot become active system knowledge; and a historical answer is tagged as either what was known at the time or a recomputation with the knowledge approved today. The homepage demo (simulated data) shows the mechanism with a support policy that moves from a 24-hour to a 4-hour response, effective midnight 25 September. Until the owner approves the change, agents keep answering under the old rule, v1. After approval, new questions use v2, and answers already given under v1 stay on record, unrewritten. Applied to a revenue definition: record both dates, and make every historical query say which of the two it wants.

How do grain and join paths inflate or shrink a total?

Grain is the answer to "what does one row represent?" Change it through a join and the SUM changes with it. An order worth 10,000 with three shipments appears once in the orders table and three times after an order-to-shipment join; SUM(order_value) then returns 30,000 (illustrative). That is a fan-out. Its mirror image, the chasm trap, appears when two fact tables joined through a shared dimension multiply against each other. Neither error raises a warning. The query runs, the dashboard renders, and the figure is simply too large.

The join path decides who the revenue belongs to. If the CRM calls the account "Acme Ltd" and the ERP's customer master calls it "ACME Limited", a join on name splits one customer into two; a fuzzy match can just as easily fuse two unrelated companies. The total may survive, but the ranking, the concentration analysis and every intercompany filter built on customer identity will not.

Semantic tooling handles both problems by making the structure explicit instead of leaving it to each query author. dbt Labs documents that MetricFlow, the engine behind the dbt Semantic Layer, uses entity types to navigate joins and avoid fan-out and chasm joins (dbt Developer Hub, About MetricFlow, as documented on November 2026). DataFocus's ontology documentation applies the same discipline to objects: orders connect to customers, production tasks and effective contracts; each field has a source and each definition an owner; uncertain relationships stay unconfirmed. Its homepage demo (simulated data) extends that last rule to identity: until a customer identifier mapping across CRM, ERP and contracts is confirmed, agents do not treat two similar names as one company. A confirmed mapping is what lets "revenue by customer" return the same answer from the invoice side and the opportunity side.

Reconciling a 1.3M gap line by line (hypothetical)

Consider a hypothetical mid-size equipment distributor at its Q3 review. Sales reports 12.4M; Finance reports 11.1M. Someone puts the two queries side by side. All figures are invented for illustration.

ChoiceGap explainedWhat the two queries did differently
Time basis0.6MSales counts orders by signature date; two large orders signed on 29 and 30 September were invoiced in October
Filters0.4MSales includes orders later cancelled and a credit note issued in September; Finance excludes both
Grain0.2MThe Sales dashboard joins orders to shipments to show delivery status; multi-shipment orders are summed once per shipment
Join path0.1MOne customer exists twice in the CRM; an intercompany sale is booked against the duplicate, so the intercompany filter misses it
Total1.3M

Four lines account for the whole gap, and no one was careless. Two of them (time basis and the cancellation filter) are legitimate differences in what each team needs to know. The other two (shipment fan-out and the duplicate customer) are errors, and would be errors in any report.

The resolution is not to declare a winner but to publish two named, owned definitions and retire the anonymous copies:

  • Bookings: order value by signature date, excluding cancelled orders, at order grain, owned by Sales Operations.
  • Recognized revenue: invoice value by recognition date, net of credit notes and intercompany, at invoice-line grain, through the confirmed customer mapping, owned by Finance.

Both can sit side by side on every dashboard. An AI assistant asked "what was Q3 revenue?" should ask which is meant, or default to the owned definition labeled "Revenue". What it must not do is write a fresh query and add a third number to the argument.

The recognized-revenue logic, sketched (illustrative, not run; names are invented):

-- Illustrative, not run
SELECT DATE_TRUNC('quarter', il.recognition_date) AS quarter,
       SUM(il.net_amount)                         AS recognized_revenue
FROM invoice_lines il
JOIN customer_map cm ON cm.erp_customer_id = il.customer_id
WHERE il.is_credit_note = FALSE
  AND cm.is_intercompany = FALSE
GROUP BY 1;

Any analyst could write this in five minutes. The value is not the SQL; it is that exactly one copy exists and every report calls it.

What does "define once, reuse everywhere" require in practice?

Most organizations already have revenue defined in prose somewhere: a finance policy, a wiki page, a comment at the top of a SQL file. Prose does not stop divergence, because each report author reads it and writes a new query, remaking all five choices. A definition only removes disagreement when it meets four conditions:

  1. Executable. Consumers call it; they do not transcribe it.
  2. Owned. One named person approves changes and rules on variants.
  3. Versioned with two dates. Effective time and approval time are both recorded, so historical answers can be reproduced.
  4. Callable by machines as well as people. If an AI agent can only read the prose, it will do what every human analyst did and write its own SQL.

Two publicly documented approaches meet these conditions in different ways.

A metrics layer in the transformation stack. dbt Labs documents that moving metric definitions out of the BI layer and into the modeling layer lets different business units work from the same definitions, and that when a metric definition changes in dbt, "it's refreshed everywhere it's invoked" (dbt Developer Hub, dbt Semantic Layer, as documented on November 2026). Its metric types are simple, ratio, cumulative, derived and conversion; a derived metric can compute gross profit as revenue minus cost (dbt Developer Hub, Metrics overview, as documented on November 2026). For a team that already models in dbt and serves mainly BI tools, this is the well-trodden path.

A business ontology. DataFocus describes its ontology as holding policies, rules and metric definitions in a document center, linked to the data they govern. A business definition is set once and used by search analysis, dashboards, control panels, the ontology agent and external agents, which all get the same figures. Every query returns the SQL that ran, the tables it used and a data fingerprint, so a disputed number is traced to the exact computation rather than argued about. DataFocus's product brief shows this with the product's built-in sample data: a business user asking for amount by requesting department in Data Search and Claude Code asking the same question through MCP got the same three departmental totals, and the agent's answer cited the SQL it ran and a receipt fingerprint, evidenceRowsSha256. That is the fourth condition made concrete: the agent did not write its own definition; it called the shared one and showed its work.

The difference between the two approaches is scope. A metrics layer centers on measures and dimensions over modeled tables. An ontology also carries the objects, their relationships, the business rules and each one's approval history. A decision rule: if your disputes are about measures and dimensions, a metrics layer is enough; if they are also about which records belong to which customer, which rule was in force in October, or what an external agent may answer, you need the wider structure. Either way, the property that ends the argument is the same: one definition, one owner, many callers.

In what order should you triage the next dispute?

When two numbers disagree, run these checks before anyone argues about which is right. The order is deliberate: each step is cheaper than the next, and a mismatch found early makes the later steps unnecessary.

  1. Name the source system behind each figure. If they differ, stop; you are comparing two datasets, not two queries.
  2. Name the date column each query filters and groups by.
  3. List the filters: status, record type, intercompany, currency, test accounts.
  4. State the grain of the rows being summed and look for a join to a finer-grained table.
  5. Check the entity mapping used to join customers, products or accounts.
  6. Check the rule version: which definition was in force for the period, and was it approved when the report ran.
  7. Apply the two-metrics test. If both teams would still want their own number after seeing the other's definition, you have two metrics; name both. If one team concedes once the choice is visible, you have one metric and an error; fix it.
  8. Write the result down as an executable definition with an owner and an effective date, and retire the copies.

Step 7 is the one most reconciliations skip. Treating a legitimate second metric as an error breeds shadow spreadsheets; treating an error as a legitimate variant breeds permanent disagreement.

When centralizing is not worth it yet

Centralized definitions have a running cost. Someone must own each metric, approve changes, field requests for variants and explain why a dashboard moved when a rule did. For a small team with one BI tool and a handful of reports, a single reviewed SQL file in version control may be all the governance needed. Exploratory analysis also benefits from freedom; routing every one-off question through a governed definition slows the people whose job is to ask new ones.

A workable threshold: centralize a metric once it appears in a recurring report read by more than one team, or once an AI agent is expected to answer questions about it. Below that line, divergence is cheap to tolerate. Above it, each reconciliation meeting costs more than the definition would have.

Next step

To see how a business ontology holds metric definitions, rules and their links to data, read the DataFocus OT product brief. For the metrics-layer approach, dbt's metrics overview is the clearest starting point.