HR Analytics Dashboard in Power BI

Data Analytics project 11

HR Analytics Dashboard in Power BI

Build a complete hr analytics dashboard portfolio project with documented metrics, reproducible DAX code, validation checks, and responsible interpretation.

Explore Data Analytics training in VizagView all project ideas

Business question

How can headcount, hires, exits, attrition, and tenure be measured correctly for selected dates?

Dataset and grain

A governed employee table with one row per employee and documented hire, exit, department, and demographic fields.

Requirements

Power BI Desktop with an employee dimension, continuous date table, and inactive date relationships to HireDate and ExitDate.

Method and validation checks

Use explicit relationship activation for hire and exit events, calculate active headcount as of the selected date, and divide exits by average monthly headcount.

  • Hire and exit dates use separate relationship logic
  • Employees exiting after the as-of date remain active
  • Attrition denominator is average headcount, not ending headcount by accident
  • Small demographic groups are suppressed

Complete DAX code

Save the code as da_hr_analytics_dashboard.dax. Review the stated model and field assumptions before using another dataset.

// Power BI model assumptions
// DimEmployee: EmployeeID, HireDate, ExitDate, Department, Gender, BirthDate
// DimDate: Date, MonthStart. Keep both employee-date relationships inactive;
// measures activate the required relationship explicitly.

Employees Hired =
CALCULATE (
    DISTINCTCOUNT ( DimEmployee[EmployeeID] ),
    USERELATIONSHIP ( DimDate[Date], DimEmployee[HireDate] )
)

Employees Exited =
CALCULATE (
    DISTINCTCOUNT ( DimEmployee[EmployeeID] ),
    USERELATIONSHIP ( DimDate[Date], DimEmployee[ExitDate] )
)

Active Headcount As Of =
VAR AsOfDate = MAX ( DimDate[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( DimEmployee[EmployeeID] ),
    FILTER (
        ALL ( DimEmployee[HireDate], DimEmployee[ExitDate] ),
        DimEmployee[HireDate] <= AsOfDate &&
        ( ISBLANK ( DimEmployee[ExitDate] ) || DimEmployee[ExitDate] > AsOfDate )
    )
)

Average Monthly Headcount =
AVERAGEX ( VALUES ( DimDate[MonthStart] ), [Active Headcount As Of] )

Attrition Rate =
DIVIDE ( [Employees Exited], [Average Monthly Headcount] )

Average Tenure Years As Of =
VAR AsOfDate = MAX ( DimDate[Date] )
RETURN
AVERAGEX (
    FILTER ( DimEmployee, DimEmployee[HireDate] <= AsOfDate ),
    DIVIDE (
        DATEDIFF (
            DimEmployee[HireDate],
            IF ( ISBLANK ( DimEmployee[ExitDate] ) || DimEmployee[ExitDate] > AsOfDate, AsOfDate, DimEmployee[ExitDate] ),
            DAY
        ),
        365.2425
    )
)

// Suppress very small groups and review demographic cuts with HR/privacy owners.

Build and run the project

Create the measures, validate headcount on known as-of dates, then build hiring, exits, attrition, tenure, and department views.

  1. Confirm the source grain and field definitions.
  2. Reconcile record counts and additive totals.
  3. Validate rate denominators and date filters.
  4. Review outliers and missing values.
  5. Read the interpretation limits before sharing conclusions.

Expected analytical output

DAX measures for hires, exits, active headcount, average monthly headcount, attrition rate, and average tenure.

Interpretation and responsible-use limits

HR dashboards contain sensitive data and can influence people. Apply access controls, legal and HR review, small-cell suppression, bias checks, and clear definitions; never rank individuals unfairly.

Ways to extend the project

Add authorised RLS, workforce plan variance, absence definitions, recruiting funnel, data freshness, and a protected metric dictionary.

Continue learning Data Analytics

Try the next project, return to the Softenant project library, or explore the Data Analytics course in Vizag for guided SQL, Excel, Power BI, Python, dashboard, and portfolio practice.