Dead stock and slow-moving inventory analysis

This report identifies variants that are active and in stock but have not been sold within a selected period — or have never been sold. The goal is to surface slow-moving and dead stock so that action can be taken: markdowns, clearance, bundling, or purchasing reviews to free up warehouse space and recover tied-up capital.

It is most useful during monthly or quarterly inventory reviews, or whenever stock turnover and warehouse capacity are being evaluated.

This article covers analysis at the variant level. For analysis at the product or SKU level, see Variations and extensions below.

In this article

• How the report works
• Steps to build the report
• Interpreting the results
• Variations and extensions
• Need more support?

How the report works

The report is built on the Variants primary table, where each row represents a single variant. This is the correct table for this analysis because it includes all variants regardless of whether they have sold, making it possible to surface variants with no recent sales activity alongside their current inventory levels and variant-level data such as price and cost. The report starts from the built-in Inventory by variant report, which already includes the key dimensions and measures needed: Product title, Variant title, and Total quantity.

Two custom fields are required:

• Last sold date: the Variants table does not include a built-in field for the last sale date. This custom dimension uses a correlated subquery to pull the most recent sale date from the Agreement lines table at the variant level.
• Days since last sold: compares the date returned by Last sold date to the current date and returns the number of days elapsed. This field updates automatically each time the report is run.

Both fields return no value for variants that have never been sold — these variants are captured by a separate OR filter condition rather than the days threshold.

The report is filtered to show active, in-stock variants where Days since last sold ≥ 180  or Days since last sold is in [Unknown] . The 180-day threshold can be adjusted to match your sales cycle or inventory review process.

Steps to build the report

Step 1 — Open the Inventory by variant report

1. Open the Reports tab
2. Open the Inventory by variant report in the Variants reports section

Step 2 — Create the Last sold date custom field

This custom dimension uses a correlated subquery to return the most recent sale date from the Agreement lines table for each variant. If a variant has never been sold, the field returns no value.

1. Select Edit, then + Create new, then Create Dimension
2. Set the Primary table to Variant
3. Set the Display name to Last sold date         
4. Set Description to Displays the most recent sale date for the variant
5. Set the Data type to Date & Time
6. Enter the following calculation:

(SELECT MAX([i.Date])
FROM [$.AgreementLines i]
WHERE [i.Kind] = 'Sale')

7. Select Save

Step 3 — Create the Days since last sold custom field

This custom dimension calculates the number of days between the Last sold date and the current date. It updates automatically each time the report is run.

1. Select Edit, then + Create new, then Create Dimension
2. Set the Primary table to Variant
3. Set the Display name to Days since last sold         
4. Set the Description to Calculates the number of days since the variant last sold
5. Set the Data type to Number and Numeric type to Number
6. Enter the following calculation:

DATEDIFF(DAY, [$.LastSoldDate], TODAY()) 

7. Select Save

Step 4 — Add fields to the report

Add the following fields to the report.

Step 5 — Add filters

This report requires both AND and OR filter logic. See Filter a report for guidance on combining filter conditions.

To restrict the report to active, in-stock variants:

1. Select Add filter, choose Status from the Products table, and select active from the value list
2. Select Add filter, choose Inventory quantity, set the operator to greater than, and enter 0

To restrict the report to variants with no sales in the last 180 days or variants that have never sold:

1. Select Add filter, choose Days since last sold, set the operator to greater than or equal, and enter 180
2. Hover over the Days since last sold filter and select + OR
3. Choose Days since last sold again and select [Unknown] from the value list

The OR condition is needed because variants that have never been sold have no value in Days since last sold and would not be captured by the ≥ 180  filter alone.

Step 6 — Sort the report

Adjust the sort order to suit your needs. Sorting by Days since last sold descending shows the variant that has not sold for the longest period first. Sorting ascending shows variants that have never sold first, followed by those most recently sold.

Step 7 — Save the report

Select Save as and save a copy of the report with a name such as Variants with no sales in the last 180 days.

Interpreting the results

Each row represents an active variant that is currently in stock and has either not been sold in the last 180 days or has never been sold. Note that the report cannot show whether a variant was active and in stock for the entire period, or when the most recent restock occurred — a variant with no recent sales may have been replenished recently.

Useful patterns to look for:

• Days since last sold is unknown — the variant has never sold. Review whether the item is newly launched or carrying inventory that has never generated demand. To investigate further, add the Created at date to the report to see when the variant was first created.
• Very high days since last sold — the variant has been dormant for an extended period. Consider discounting, bundling, transferring stock, or removing the variant from active sale if demand is unlikely to return.
• Variants with substantial inventory quantity — these represent stock tying up the most capital and warehouse space. Prioritise these variants when planning inventory reduction or clearance activity.
• Multiple variants of the same product appearing in the report — this can indicate that specific sizes, colours, or configurations are underperforming while other variants of the same product continue to sell. Review whether the product assortment should be simplified.
• Recently crossed the 180-day threshold — variants with values just above 180 may warrant monitoring rather than immediate action, particularly if they are seasonal or have irregular purchasing patterns.

Variations and extensions

Show the worst variants per product type or vendor

Add Product Type or Vendor as a dimension and adjust the sort hierarchy to group by that field first, followed by the Days since last sold field descending. This shows the most dormant variants within each segment — useful for identifying which product types or vendors have the most slow-moving inventory.

Variants that have never been sold will appear last within each group under this sort order, since they have no value in Days since last sold. To show these variants first within each group, sort Days since last sold ascending instead.

Analyse by product instead of variant

The base report identifies individual variants that have not sold recently. If you want to identify products where no variants have sold in the last 180 days, you can build a product-level version of the report.

Start from the built-in Inventory by product report instead of Inventory by variant. Recreate the Last sold date and Days since last sold custom fields from Steps 2 and 3, but set the primary table to Products for both — this changes the correlated subquery to join at the product id level rather than the variant id level, so the most recent sale date is calculated across all variants of a product rather than per variant.

To filter for products that still have stock, a new custom dimension is needed to aggregate inventory across all variants belonging to a product, since inventory lives at the variant level. Create a new dimension on the Products primary table with the display name Inventory quantity  and the following calculation:

(
SELECT SUMZ([v.InventoryQuantity])
FROM [$.Variants v]
)

Then repeat Steps 4–6 using the product-level equivalents of each field.

Analyse by SKU instead of variant

To group results by SKU rather than individual variant — useful when multiple variants share a SKU and should be treated as a single unit — start from the built-in Inventory by SKU report and create the following alternative custom fields.

The Last sold date and Days since last sold fields need to use the most recent sale date across all variants sharing a product title and SKU, rather than per variant. Create these as new custom dimensions on the Variants primary table:

A new inventory quantity field is also needed to sum stock across all variants sharing a product title and SKU, for use in the in-stock filter. Create a new dimension on the Variants primary table with the display name Product SKU inventory quantity , the description Aggregates inventory to the product SKU level , the data type Number and numeric type Integer, and the following calculation:

SUMZ([v.InventoryQuantity]) 
OVER (PARTITION BY [$.Product.Title],[$.SKU])

Then repeat Steps 4–6 using the SKU-level equivalents of each field.