Power BI - Essential HR DAX Formulas

on

 


Essential HR DAX Formulas

1. Headcount

Headcount = COUNTROWS(EmployeeTable)

Counts the total number of employees.

2. New Hires

New Hires = 
CALCULATE(COUNTROWS(EmployeeTable), 
          EmployeeTable[HireDate] >= MIN(DateTable[Date]) && 
          EmployeeTable[HireDate] <= MAX(DateTable[Date]))

Tracks employees hired in a given period.

3. Attrition Rate

Attrition Rate = 
DIVIDE(
    COUNTROWS(FILTER(EmployeeTable, EmployeeTable[ExitDate] <> BLANK())),
    [Headcount],
    0
)

Measures percentage of employees who left.

4. Average Tenure

Average Tenure = 
AVERAGEX(
    EmployeeTable,
    DATEDIFF(EmployeeTable[HireDate], COALESCE(EmployeeTable[ExitDate], TODAY()), YEAR)
)

Shows average years employees stay.

5. Diversity Metrics

Female Employees = CALCULATE(COUNTROWS(EmployeeTable), EmployeeTable[Gender] = "Female")
Male Employees = CALCULATE(COUNTROWS(EmployeeTable), EmployeeTable[Gender] = "Male")

Breakdown by gender (extend for age, ethnicity, etc.).

6. Absenteeism Rate

Absenteeism Rate = 
DIVIDE(
    SUM(AttendanceTable[AbsentDays]),
    SUM(AttendanceTable[WorkingDays]),
    0
)

Tracks absence percentage.

7. Training Hours per Employee

Training Hours per Employee = 
DIVIDE(
    SUM(TrainingTable[Hours]),
    [Headcount],
    0
)

Measures average training investment.

8. Employee Satisfaction Index

(Assuming survey scores are stored)

Avg Satisfaction = AVERAGE(SurveyTable[Score])

How These Formulas Fit in HR Dashboards

  • Workforce Overview → Headcount, Diversity, Tenure
  • Recruitment → New Hires, Time-to-Hire (custom DAX with date differences)
  • Retention → Attrition Rate, Exit Reasons
  • Engagement → Satisfaction Index, Training Hours
  • Attendance → Absenteeism Rate

HR DAX Formulas with Date Intelligence

1. Headcount Over Time

Headcount = COUNTROWS(EmployeeTable)

Headcount YTD = 
TOTALYTD(
    [Headcount],
    DateTable[Date]
)

Headcount LY = 
CALCULATE(
    [Headcount],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

 Compare current year headcount vs. last year.

2. New Hires Trend

New Hires = 
CALCULATE(
    COUNTROWS(EmployeeTable),
    NOT(ISBLANK(EmployeeTable[HireDate]))
)

New Hires MTD = 
CALCULATE(
    [New Hires],
    DATESMTD(DateTable[Date])
)

New Hires YTD = 
TOTALYTD(
    [New Hires],
    DateTable[Date]
)

 Shows monthly and year-to-date hiring activity.

3. Attrition Rate with Time Intelligence

Attrition = 
CALCULATE(
    COUNTROWS(EmployeeTable),
    NOT(ISBLANK(EmployeeTable[ExitDate]))
)

Attrition Rate = 
DIVIDE([Attrition], [Headcount], 0)

Attrition Rate YTD = 
TOTALYTD(
    [Attrition Rate],
    DateTable[Date]
)

Attrition Rate LY = 
CALCULATE(
    [Attrition Rate],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

Compare attrition trends across years.

4. Average Tenure Trend

Average Tenure = 
AVERAGEX(
    EmployeeTable,
    DATEDIFF(EmployeeTable[HireDate], COALESCE(EmployeeTable[ExitDate], TODAY()), YEAR)
)

Average Tenure YTD = 
TOTALYTD(
    [Average Tenure],
    DateTable[Date]
)

 Tracks how tenure evolves over time.

5. Absenteeism Rate

Absenteeism Rate = 
DIVIDE(
    SUM(AttendanceTable[AbsentDays]),
    SUM(AttendanceTable[WorkingDays]),
    0
)

Absenteeism Rate MTD = 
CALCULATE(
    [Absenteeism Rate],
    DATESMTD(DateTable[Date])
)

Absenteeism Rate YTD = 
TOTALYTD(
    [Absenteeism Rate],
    DateTable[Date]
)

 Monitors absenteeism monthly and yearly.

6. Employee Satisfaction (Survey Scores)

Avg Satisfaction = AVERAGE(SurveyTable[Score])

Avg Satisfaction YTD = 
TOTALYTD(
    [Avg Satisfaction],
    DateTable[Date]
)

Avg Satisfaction LY = 
CALCULATE(
    [Avg Satisfaction],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

 Compare engagement scores year-over-year.

Takeaway

By combining HR KPIs with Date Intelligence functions (TOTALYTD, DATESMTD, SAMEPERIODLASTYEAR), you can show trends, comparisons, and growth patterns—making your HR dashboard not just descriptive but predictive.


Comments

Post a Comment