Plain-language questions
Ask for average absence duration per month, customer or product in the SQL Explorer with AI. The AI writes the SQL, you see the query and run it yourself; the AI only receives metadata.
KPI · HR
Average absence duration: formula and example.
Average absence duration is the average number of days a sickness spell lasts, measured over spells that ended in the period. You divide the total length of those spells by their number. It shows whether absence is mostly short or mostly long-term.
Average absence duration formula
Total calendar days of closed absence spells ÷ number of closed absence spells
Worked example
In 2025, 176 absence spells ended at Acme Groothandel, adding up to 1,584 calendar days. The average absence duration is 1,584 ÷ 176 = 9.0 days.
Fictitious figures for Acme Groothandel, a sample wholesaler.
How to read this KPI
A longer average means recovery takes longer or there is more long-term absence. A single long spell can pull the average up considerably, so also look at the median and at the split between short (up to a week), medium and long spells. Compare with previous years and read it next to absence frequency.
Pitfalls with average absence duration
- Including open spells with their duration so far, which artificially lowers the average.
- Showing only the average when a few outliers drive the picture.
- Sharing duration per person widely; an individual's absence history is sensitive and stays with the manager and occupational health.
SQL & DAX
Calculating average absence duration in SQL and DAX
Two examples on a fictitious data warehouse for Acme Groothandel: a PostgreSQL query and a measure for a Power BI model. Adjust table and column names to your own model.
SELECT date_trunc('year', end_date)::date AS year,
COUNT(*) AS closed_spells,
ROUND(AVG(end_date - start_date + 1), 1) AS avg_calendar_days,
ROUND(AVG(sick_days), 1) AS avg_sick_days,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY end_date - start_date + 1) AS median_calendar_days
FROM absences
WHERE end_date IS NOT NULL
GROUP BY 1
ORDER BY 1;Closed Spells = CALCULATE ( COUNTROWS ( Absences ), NOT ISBLANK ( Absences[EndDate] ) )
Avg Absence Duration =
DIVIDE (
SUMX ( FILTER ( Absences, NOT ISBLANK ( Absences[EndDate] ) ), DATEDIFF ( Absences[StartDate], Absences[EndDate], DAY ) + 1 ),
[Closed Spells]
)In ENABLE
Tracking this KPI in ENABLE
In your Power BI dashboard
Define the KPI as a measure in your Power BI model and show it in the portal, with "Last refreshed" next to it. Access profiles and optional row-level security decide who sees which figures.
Custom dashboards (pilot)
Add the KPI as a tile to a custom dashboard, compared with last year. The Dashboard Builder is a pilot and can be switched on per client.
Monthly targets
With the built-in Targets app you set a target for the KPI per month, so you see actuals and target side by side.
FAQ
Frequently asked questions
How do you calculate average absence duration?
Add up the length of all absence spells that ended in the period and divide by the number of closed spells. It is usually measured in calendar days, from the first day of sickness up to and including the last. Open spells are left out.
Why look at the median as well as the average absence duration?
Because one long-term spell can raise the average sharply. The median shows how long a typical spell lasts. If the average and the median are far apart, a few long cases are driving the figure.
Should absence duration use calendar days or working days?
Both are used, but calendar days are the most common for the length of a spell. What matters more is choosing one and using it everywhere. State the choice next to the figure.
Further reading
- KPI libraryHR KPIs: formulas and examplesHR KPIs cover staffing, absence, turnover and recruitment. This is sensitive data: show it only to people who are allowed to see it, and do not report on groups so small that individuals can be identified.Read more
- ResourcesHow to build a KPI dashboard: steps and examplesA KPI dashboard is an overview of the key figures you use to steer towards your goals, such as revenue, margin, on-time delivery or absenteeism. A good KPI dashboard does not start with the software, but with the decisions it needs to support. You then choose a limited number of KPIs, define them, sketch the dashboard and only then build it.Read more
- SolutionPower BI dashboard portal: share dashboards securelyShare your Power BI dashboards securely in your own portal, with role-based access.Read more
Use AI on your own data. Securely, and without the headache.
In an online demo we show how ENABLE makes your data securely available and how you build on it: describing an app in the AI Builder, plain-language questions, dashboards and APIs. And how we set it up and manage it for you.