Why two reports give two numbers — and how to fix it

Cause one: different definitions

The commonest by a distance. Sales counts revenue when the deal is signed; finance counts it when it is recognised. One includes VAT, one does not. One nets off returns and credits, one reports gross. Both are right within their own frame, and neither wrote the frame down.

The same disease affects the words everyone assumes are obvious. What is an active customer — one who bought this year, one with a live contract, one who logged in this month? We have watched three directors in one room give three answers and each be surprised the others differed.

Cause two: different sources

One report reads the CRM directly, another reads the warehouse copy that syncs overnight, a third reads a finance extract. Each is a legitimate source and they hold slightly different states of the same world. Add a system that fails silently once a fortnight and the drift becomes permanent.

Cause three: timing

Two reports run four hours apart against data that changed in between produce two numbers, and both are correct. This one is nearly always missed because people compare the numbers rather than the timestamps.

The remedy is small and effective: put the "data as at" time on the face of every report, and make it a required element of any report promoted to a trusted tier. It ends a surprising share of these arguments in the first thirty seconds.

Cause four: undeclared filters

One report excludes internal test accounts; the other never had that filter added. One drops cancelled orders, another includes them until they are refunded. One covers the trading entity, the other the group. Filters accumulate quietly, usually as a fix for a specific complaint, and nobody records them.

Why a semantic layer is only part of the answer

A semantic layer — a Power BI shared dataset, a dbt metrics layer, a warehouse view everything reads — is a genuinely good idea. It defines a measure once so every report inherits it, and it fixes cause one and much of cause four.

What it cannot do is decide what the measure should be. That is a business decision with a winner and a loser, and it is the reason semantic layer projects stall: the technical work is a fortnight, and then everything waits three months for somebody senior enough to say that net revenue means this, and that the other version is now called something else.

So the sequence matters. Agree the definitions, then implement them. Implementing first produces a semantic layer with four measures called revenue, which is the original problem in a more expensive container.

The twenty-definition exercise

This is the highest-value fortnight available to most businesses in this position, and it needs no purchase.

1. Take the last three board packs and list every distinct measure. You will get 25 to 40. Cut to the twenty that decisions are actually made on.
2. For each, write: the plain-English definition, the formula, the source system, the filters applied, the owner, and the period convention (calendar or fiscal, and how partial periods are handled).
3. Circulate the draft to the people who use each measure and collect the disagreements. Expect three or four real ones. Those are the whole point; the other sixteen were never in dispute.
4. Take the disputed ones to the sponsor and let them decide. Not a committee. One person with the standing to make the other version wrong.
5. Rename the losing variants rather than deleting them. "Signed revenue" and "recognised revenue" can coexist happily. What cannot coexist is two things both called revenue.
6. Publish the definitions where the reports are, and make the measure name in the report link to it.

Two weeks, mostly other people's calendars. The result is that the next meeting spends its time on the decision rather than on the reconciliation.

Certifying the small number of reports that matter

Once definitions exist, label the reports. Power BI's endorsement feature does this natively, and any BI tool can do it with a naming convention if not.

Two rules make it work. Certification requires a named owner's sign-off, not a request from the report's author. And there has to be a way down — a certified report whose owner has left and whose checks have stopped running gets demoted, publicly. The first demotion is what makes every other badge credible.

What this does not fix

If two systems genuinely hold different facts about the same customer, definitions will not reconcile them — that is a master data problem, and it needs a decision about which system is authoritative for which attribute. And if the underlying data is simply wrong, a single agreed definition gives you one wrong number instead of two, which is progress but not a fix. That one is quality.