Follow one invoice from source records to a reporting check
Turing Demo Wholesale: fictional business, customers and financial results
Published 25 October 2026. Reporting cutoff: 30 September 2026. Currency: GBP.

1. Establish the row each file represents

invoices.csv has one row per invoice. invoice-lines.csv has two rows per invoice.
payment-allocations.csv has one row per allocated payment, including payments after
the cutoff. Identifier fields are text. Raw monetary fields ending Pence are integer
pence; divide by 100 once when displaying GBP. Prepared reporting tables already
use GBP decimals. They must retain their own reporting grains.

2. Trace invoice INV-0001

Customer C001; posted 1 January 2025; due 31 January 2025.
Net amount: 76,000 pence = GBP 760.00.
Illustrative VAT: 15,200 pence = GBP 152.00.
Gross amount: 91,200 pence = GBP 912.00.

The two invoice lines contain net amounts of GBP 120.00 and GBP 640.00.
Their illustrative VAT amounts are GBP 24.00 and GBP 128.00.
The line totals reconcile to the invoice header. Every line in this teaching data
uses an assumed 20 percent VAT rate; the data is not tax guidance.

Allocation ALLOC-0001-1: 19 January 2025, GBP 547.20.
Allocation ALLOC-0001-2: 12 February 2025, GBP 364.80.
Both precede the reporting cutoff. Together they allocate GBP 912.00.
At the cutoff, this invoice has GBP 0.00 outstanding and GBP 0.00 overdue.
These allocations describe payments against this invoice, not all business cash.

3. See why a direct join can double an invoice

Joining the invoice header to the two allocation rows repeats its GBP 912.00 gross
amount twice. Summing that repeated header produces GBP 1,824.00.
Adding the two invoice lines to the same flattened join multiplies rows again.
COUNT(DISTINCT InvoiceID) can repair a count while leaving the amount incorrect.

Aggregate allocations to one row per invoice before combining invoice-level totals.
The accompanying raw-invoice-reconciliation.sql demonstrates that route. The query
was executed independently in SQLite against the raw CSVs. It was not executed as
a raw-source query inside Power BI or Zoho Analytics. Check dialect, types and
relationships before adapting it to another platform.

4. Reconcile the full invoice cohort

The query returns 520 posted invoices from 534 source invoices; 14 voids are excluded.
Only allocations on or before 30 September 2026 reduce the cutoff balance.
Overdue means the due date is before the cutoff and a balance remains.

Sum of gross invoiced: GBP 608,388.00.
Sum of allocations by cutoff: GBP 468,002.40.
Sum of outstanding: GBP 140,385.60.
Sum of overdue: GBP 116,863.20.

Gross invoiced minus allocated equals outstanding. Overdue is part of outstanding.
Gross invoice amounts include illustrative VAT. Net sales excludes it. Neither is
a statement about recognised revenue. A single cutoff is not a historical ageing
trend; recalculate the allocation and due-date conditions for each earlier cutoff.

5. Inspect the native reporting evidence

Power BI imported the prepared 63-row sales table and checked native sales, cost,
gross profit and invoice-count sums. Zoho Analytics imported the same sales table
and the 18-row prepared collections table, then checked totals in a saved Query Table.
Both agree with the separately calculated source controls within the recorded scope.

Native captures and execution record:
https://turingbi.com/solutions/power-bi-consultant-uk#native-report-example
https://turingbi.com/solutions/zoho-analytics-partner-uk#native-report-example
https://turingbi.com/proof/bi-portfolio-demo/execution-checks.txt

The native checks use prepared CSV imports and aggregation. Raw-source joins, live
connectors, scheduled refresh and recipient permissions need their own native tests.
For a project, record the actual source, query version, report filters, refresh time,
recipient role and expected result alongside each acceptance check.

Reproduce the source checks from the repository:
python3 scripts/build-bi-portfolio-demo.py
The builder checks both the correct invoice-level query and the duplicated direct
join for INV-0001, then recreates the same downloadable data pack.
