Validate a management dashboard against ERP data

1. Define the metric people are comparing

The example metric is September posted invoice amount, excluding tax, in CAD. It includes credit notes as signed negative amounts. It is an operational control total: it does not claim to measure cash collected, recognized revenue or profitability.

The grain is one invoice or credit note per row. The reporting window is 1 September 2026 at 00:00 UTC up to, but excluding, 1 October 2026 at 00:00 UTC. Choosing UTC explicitly avoids silently mixing dates from different time zones; a real business may need another agreed time zone.

• Amount: signed invoice subtotal, excluding tax; integer cents.
• Status: posted only; drafts excluded.
• Currency: CAD only; USD stays separate with no invented exchange rate.
• Grain: one unique document ID per row, with no line-level join duplication.
• Window: September 2026 in UTC, using an exclusive end boundary.

2. Reconcile the displayed total to its records

Adding every row in the synthetic export gives 3,000.00 across mixed statuses, currencies and periods. The defined metric is CAD 1,800.00 across three included documents: 1,200.00 + 800.00 − 200.00. The difference has a complete explanation, rather than being dismissed as a dashboard problem.

The credit note must retain its sign. Counting it as a positive invoice would produce CAD 2,200.00. Counting rows after joining each invoice to several lines could inflate the total again. Check the grain before aggregating.

3. Make the query match the written definition

This SQL runs against a table named demo_invoices containing the downloaded CSV columns. It assumes issued_at contains normalized UTC ISO timestamps in the same fixed format. It deliberately makes no assumptions about a live ERP's tables, accounting models or permissions.

The accompanying Python script uses standard-library CSV and timezone-aware date parsing, rejects duplicate document IDs and reports included IDs and excluded rows. Integer cents keep this small example's arithmetic exact. Keep the definition beside the calculation so a later filter change is visible to reviewers.

SELECT SUM(subtotal_cents) AS september_cad_cents
FROM demo_invoices
WHERE status = 'posted'
  AND currency = 'CAD'
  AND issued_at >= '2026-09-01T00:00:00Z'
  AND issued_at <  '2026-10-01T00:00:00Z';

4. Check data quality before deciding from the chart

A correct formula cannot repair an incomplete source extract. Check missing IDs, duplicate keys, unexpected statuses, valid amounts, date parsing and refresh completeness before accepting the report. Confirm that the source totals and dashboard use the same snapshot and access scope.

Then review the excluded records. An exception table helps distinguish an intended filter from a missing transaction. In this example, the October invoice, draft and USD invoice are legitimate exclusions under the stated definition. A missing posted CAD invoice would require investigation rather than a new filter to make the totals agree.

5. Turn the reconciliation into a maintained report

Show the total with the reporting period, currency, refresh timestamp and a link or drill-down to its records. Keep a short definition explaining statuses, credit notes, exclusions and the owner of the metric. These details let a manager understand what action the figure can support.

A real handover should identify the data owner, refresh responsibility, access requirements and a small repeatable set of validation checks. Agree what happens when checks fail: a visible exception is often more useful than confidently displaying a stale or incomplete total. This example's answer is CAD 1,800.00; it cannot establish a business trend or improvement from one invented month.