Inventory Days on Hand
- Formula
- Average inventory value divided by COGS× 365
- Typical range
- Finished goods commonly target 30 to 60 days; raw material target depends on supplier lead time, not a general number
- Direction
- Lower is better
Open the Calculator Generate the DAX
Days on hand measures how many days of usage the current inventory balance would cover at the current consumption rate, calculated from average inventory value divided by COGS and scaled to a full year. Lower is generally better because inventory ties up cash, but “generally” is doing real work in that sentence.
What Good Looks Like
Finished goods days on hand commonly targets 30 to 60 days for discrete manufacturers, though it swings widely with order lead time expectations and demand volatility. Raw material days on hand should track supplier lead time plus a safety buffer, not a generic company-wide target; a part with a 12-week lead time needs a different DOH than one available overnight from a local distributor.
The trap with this metric is optimizing it in isolation. Driving DOH down aggressively, without watching service level and stockout rate at the same time, saves working capital right up until a supplier hiccup turns into a missed shipment. Any DOH reduction target should be reported next to fill rate or on-time delivery, not on its own page, so nobody mistakes a cash-flow win for a customer-service loss that hasn’t shown up yet.
Days on Hand in Power BI (DAX)
With a daily inventory snapshot fact and a COGS fact rolled up by period:
-- Average of the month-end balances in the last 12 months. Each month's
-- snapshot date is found with every filter but the date cleared, so a part
-- with no stock that month counts as zero instead of dropping out.
-- MonthDates is taken first: CALCULATETABLE applies the month from the row,
-- and REMOVEFILTERS would otherwise clear that month along with the rest.
Average Inventory =
CALCULATE (
VAR Months =
FILTER (
ADDCOLUMNS (
VALUES ( dim_date[month] ),
"@Snapshot",
VAR MonthDates =
CALCULATETABLE ( VALUES ( dim_date[date] ) )
RETURN
CALCULATE (
MAX ( fact_inventory_snapshot[snapshot_date] ),
REMOVEFILTERS ( fact_inventory_snapshot ),
MonthDates
)
),
NOT ISBLANK ( [@Snapshot] )
)
RETURN
DIVIDE (
SUMX (
Months,
VAR Snapshot = [@Snapshot]
RETURN
CALCULATE (
SUM ( fact_inventory_snapshot[inventory_value] ),
fact_inventory_snapshot[snapshot_date] = Snapshot
)
),
COUNTROWS ( Months )
),
DATESINPERIOD ( dim_date[date], MIN ( MAX ( dim_date[date] ), TODAY () ), -12, MONTH )
)
COGS (TTM) =
CALCULATE (
SUM ( fact_cogs[cogs_amount] ),
DATESINPERIOD ( dim_date[date], MIN ( MAX ( dim_date[date] ), TODAY () ), -12, MONTH )
)
Days on Hand =
DIVIDE ( [Average Inventory], [COGS (TTM)] ) * 365
A daily snapshot table has about 30 rows per month for the same stock, so summing a month
inflates inventory about 30 times. Average Inventory takes the last snapshot in each
month, then averages the 12 month-ends in the same window as COGS (TTM). dim_date[month]
needs one value per month of each year, such as 2026-03, not just the month name, and
dim_date should stop at the last month you have loaded, or the window runs past your data. In a table by part, a part
with no stock in a month counts as zero for that month rather than dropping out, so the
part rows add up to the total. Inventory snapshots shows
what goes wrong otherwise.
In the middle of a month the 12-month window covers 13 calendar months, so the average
takes 12 month-ends plus the latest snapshot of the current month. To average exactly 12
closed months, use MIN ( MAX ( dim_date[date] ), EOMONTH ( TODAY (), -1 ) ) as the end date in both Average Inventory and COGS (TTM), so the two windows still match.
Split this by inventory category (raw material, WIP, finished goods) rather than reporting one blended number. Each category has a different driver and a different owner.
Common Mistakes
- Using a single period-end snapshot instead of an average. Month-end inventory spikes from a receiving push or a shipping delay distort a point-in-time number badly.
- Blending raw material, WIP, and finished goods into one DOH figure. It hides which category is actually carrying the excess.
- Cutting DOH without watching service level. A falling DOH number that coincides with rising stockouts isn’t inventory efficiency, it’s inventory risk showing up on a lag.
Days on Hand Calculator
Runs in your browserResult
DAX Generator
Use My Own Table and Column NamesUpdates as You Type
-- Average of the month-end balances in the last 12 months. Each month's
-- snapshot date is found with every filter but the date cleared, so a part
-- with no stock that month counts as zero instead of dropping out.
-- MonthDates is taken first: CALCULATETABLE applies the month from the row,
-- and REMOVEFILTERS would otherwise clear that month along with the rest.
Average Inventory =
CALCULATE (
VAR Months =
FILTER (
ADDCOLUMNS (
VALUES ( dim_date[month] ),
"@Snapshot",
VAR MonthDates =
CALCULATETABLE ( VALUES ( dim_date[date] ) )
RETURN
CALCULATE (
MAX ( fact_inventory_snapshot[snapshot_date] ),
REMOVEFILTERS ( fact_inventory_snapshot ),
MonthDates
)
),
NOT ISBLANK ( [@Snapshot] )
)
RETURN
DIVIDE (
SUMX (
Months,
VAR Snapshot = [@Snapshot]
RETURN
CALCULATE (
SUM ( fact_inventory_snapshot[inventory_value] ),
fact_inventory_snapshot[snapshot_date] = Snapshot
)
),
COUNTROWS ( Months )
),
DATESINPERIOD ( dim_date[date], MIN ( MAX ( dim_date[date] ), TODAY () ), -12, MONTH )
)
COGS (TTM) =
CALCULATE (
SUM ( fact_cogs[cogs_amount] ),
DATESINPERIOD ( dim_date[date], MIN ( MAX ( dim_date[date] ), TODAY () ), -12, MONTH )
)
Days on Hand =
DIVIDE ( [Average Inventory], [COGS (TTM)] ) * 365