Combine Inventory and Sales Data Without Double-Counting

To share inventory and sales data, prepare a table that answers a specific question: how much sold during each period, and how much stock remained at its end?

The crucial distinction is time. Sales measure activity over a period; inventory measures a balance at a particular moment. Combining them is useful, but adding inventory snapshots across months produces a misleading total.

Here is a compact structure you can build in a spreadsheet, check against source records, and publish as an explorable public page.

1. Define what one row represents

Use one row per month, product, and location. If your report covers a single location, state that in the description and omit the location column.

Keep transaction-level sales and inventory records in separate working tables. Aggregate them before combining them into the public summary. This follows the periodic-summary approach described by the Kimball Group: the row represents a period, not an individual transaction.

For example, this three-row excerpt from a synthetic dataset covers one fictional location. All stock balances are measured after the final day’s processing.

month	sku	net_units_sold	net_sales_usd	closing_on_hand_units	inventory_as_of
2026-08	A001	55	1100	83	2026-08-31
2026-08	B002	10	300	90	2026-08-31
2026-09	A001	70	1400	53	2026-09-30

A SKU, or stock keeping unit, is a product identifier. Keep it as text so codes such as 00125 retain their leading zeros.

Document the measures before calculating them. For this example:

• Net units sold: shipped units minus returned units, recorded in the month each event was posted.
• Net sales USD: merchandise revenue after discounts and refunds, excluding tax and shipping charges.
• Closing on-hand units: physical stock recorded at the cutoff, including reserved stock but excluding inbound stock not yet received.

These are chosen definitions, not universal conventions. If your source uses order dates, excludes reserved inventory, or treats returns differently, preserve and explain that difference. Do not label on-hand stock as available-to-sell stock: for example, Shopify’s inventory definitions include committed and unavailable units in on-hand inventory.

2. Align the two inputs before joining

Prepare two summaries with the same keys:

• Sales: month + SKU + location, with summed units and revenue.
• Inventory: month + SKU + location, with the balance from the specified month-end cutoff.

Do not sum daily stock snapshots to create monthly closing inventory. Select the correct cutoff record. If the required snapshot is missing, mark the balance as missing rather than silently substituting an earlier date.

Before merging, confirm that each summary has only one row per key. Joining sales transactions directly to repeated inventory snapshots can multiply rows and inflate totals. For more on this failure mode, see joining complex datasets without double-counting.

In Power Query, merge on all key columns, using matching data types and selecting the columns in the same order in both tables. Microsoft’s merge documentation explains these requirements and the available join types.

For validation, use a full outer join to retain keys found in either input. When expanding the inventory table, retain its key columns too; for inventory-only rows, use those values to fill the empty sales-side keys. Review unmatched records: they could indicate missing inventory, a product with no sales, or inconsistent identifiers. Convert missing sales to zero only when the sales extract is complete and absence truly means no activity.

3. Check totals and stock movements

Run these checks before export:

• Unique keys: no duplicate month–SKU–location combinations.
• Sales reconciliation: summary units and revenue match the reviewed source totals for each period.
• Join coverage: no unexplained unmatched products or locations.
• Cutoff consistency: inventory dates and time zones match the stated reporting rule.
• Exceptions: investigate negative balances, missing values, and unexplained stock changes rather than deleting them.

Where movement records are available, reconcile physical inventory with:

Closing stock = Opening stock + Receipts + Restocked returns − Shipped units + Net adjustments

Here, receipts exclude the separately counted restocked returns. Account for transfers in and out through net adjustments, along with other corrections, without counting any movement twice. For example, 100 + 40 + 5 − 60 − 2 = 83. Returned units reduce net units sold, but increase physical stock only if they are restocked.

In the sample, A001 sold 125 net units across August and September. Its September closing stock is 53—not 83 + 53 = 136. Balance measures are generally not additive across time, a distinction explained in the Kimball Group’s guidance on additive measures.

4. Publish the reviewed summary, not the working records

Use one header row, consistent date formats, plain numeric values, and no subtotal rows inside the dataset. Export the finished summary; reopen it to verify identifiers, dates, missing values, and totals. The CSV conversion guide covers export trade-offs.

Publish only data you are authorized to make public. Exclude customer and employee identifiers, payment details, confidential costs, and commercially sensitive stock or sales figures. Aggregation alone does not guarantee that a table is safe to release.

TablePage accepts CSV, TSV, XLSX, and XLS files and generates a public dataset page with a shareable link and filterable table. Upload only the reviewed public file, then compare the displayed rows and values with your export.

Provide a title and accompanying description stating the coverage period, row unit, currency, stock cutoff, return-accounting rule, missing-value meaning, source, and refresh date. Label synthetic examples explicitly. A reader should be able to filter a product’s sales history while understanding that each inventory value is a separate point-in-time balance.