HR Dashboard Project

Table 1: Employees (fact-style — one row per employee, current and former)
  Columns: EmpID, First Name, Last Name, Department, Start Date, Leave Date,
           Salary, Status (Active / Leaver), Start Year, Tenure Years,
           Tenure Band, Salary Band

Table 2: DimDate (Date dimension — one row per calendar day)
  Columns: Date, Year, Month, MonthNum, Quarter, MonthYear
  Create this in Power Query using M:
  let
    StartDate = #date(2015, 1, 1),
    EndDate   = #date(2026, 12, 31),
    Days      = Duration.Days(EndDate - StartDate) + 1,
    DateList  = List.Dates(StartDate, Days, #duration(1,0,0,0)),
    AsTable   = Table.FromList(DateList, Splitter.SplitByNothing()),
    Typed     = Table.TransformColumnTypes(AsTable, {{"Column1", type date}}),
    Renamed   = Table.RenameColumns(Typed, {{"Column1", "Date"}}),
    AddYear   = Table.AddColumn(Renamed, "Year",     each Date.Year([Date]),   Int64.Type),
    AddMonth  = Table.AddColumn(AddYear, "Month",    each Date.ToText([Date], "MMM"), type text),
    AddMonthN = Table.AddColumn(AddMonth,"MonthNum", each Date.Month([Date]),  Int64.Type),
    AddQtr    = Table.AddColumn(AddMonthN,"Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddMY     = Table.AddColumn(AddQtr,  "MonthYear",each Date.ToText([Date], "MMM yyyy"), type text)
  in AddMY

Table 3: DimDept (Department lookup)
  Columns: Department, DeptCode, DeptHead, CostCentre
  Small manual table — 6 rows (one per department)

Relationships in Power Pivot:
  Employees[Start Date]   → DimDate[Date]   (many-to-one)
  Employees[Leave Date]   → DimDate[Date]   (many-to-one — second relationship)
  Employees[Department]   → DimDept[Department] (many-to-one)

Create a dedicated measures table called "HR Metrics":
  Power Pivot → Home → Add Table to Model → create a blank 1-row helper table
  Right-click the table in the diagram → rename "HR Metrics"
  All measures below go into this table

-- Core headcount measures --

Total Headcount =
  COUNTROWS(Employees)

Active Headcount =
  CALCULATE(COUNTROWS(Employees), Employees[Status] = "Active")

Total Leavers =
  CALCULATE(COUNTROWS(Employees), Employees[Status] = "Leaver")

New Hires This Year =
  CALCULATE(
    COUNTROWS(Employees),
    YEAR(Employees[Start Date]) = YEAR(TODAY())
  )

-- Turnover rate --
-- Industry standard: Leavers / Average Headcount during period --

Avg Headcount =
  DIVIDE(
    [Active Headcount] + [Total Headcount],
    2
  )

Turnover Rate =
  DIVIDE([Total Leavers], [Avg Headcount], 0)

-- Tenure --

Avg Tenure Years =
  AVERAGEX(
    FILTER(Employees, Employees[Status] = "Active"),
    Employees[Tenure Years]
  )

-- Salary --

Avg Salary Active =
  CALCULATE(AVERAGE(Employees[Salary]), Employees[Status] = "Active")

Total Payroll =
  CALCULATE(SUM(Employees[Salary]), Employees[Status] = "Active")

-- Dept-level metrics (used in PivotTable rows) --

Leavers This Year =
  CALCULATE(
    COUNTROWS(Employees),
    Employees[Status] = "Leaver",
    YEAR(Employees[Leave Date]) = YEAR(TODAY())
  )

Dept Turnover Rate =
  DIVIDE([Leavers This Year], [Active Headcount], 0)

Create a sheet named "PT_Helper" — all five PivotTables live here.
Right-click the sheet tab → Hide when dashboard is complete.

PivotTable 1: KPI Cards source
  Rows:    (none)
  Values:  Active Headcount, New Hires This Year, Turnover Rate, Avg Tenure Years
  Purpose: Single-row output feeding GETPIVOTDATA formulas in KPI cards

PivotTable 2: Headcount by Department
  Rows:    Department (from DimDept)
  Values:  Active Headcount
  Sort:    Values descending
  Purpose: Horizontal bar chart showing department size

PivotTable 3: Hire Trend by Month
  Rows:    MonthNum (from DimDate, sorted ascending), Month
  Filters: Year (from DimDate)
  Values:  New Hires This Year → actually use COUNTROWS filtered to [Start Date] year
           Replace with: Hires by Start Month =
             CALCULATE(COUNTROWS(Employees), YEAR(Employees[Start Date]) = SELECTEDVALUE(DimDate[Year]))
  Purpose: Column chart — hire volumes per calendar month

PivotTable 4: Turnover by Department
  Rows:    Department
  Values:  Total Leavers, Active Headcount, Dept Turnover Rate
  Purpose: Department turnover rate table (not a chart)

PivotTable 5: Tenure Distribution
  Rows:    Tenure Band (from Employees)
  Values:  Active Headcount
  Purpose: Horizontal bar chart showing tenure spread

Sheet setup:
  New sheet named "Dashboard"
  View → uncheck Gridlines, Formula Bar, Headings
  Zoom: 90%
  Background: fill A1:Z100 with #f1f5f9 (light slate)

KPI Card construction (one example — replicate for all four):
  Select B2:D5 → Merge cells
  Fill: #0f172a (dark navy)
  Formula bar (in merged cell):
    =GETPIVOTDATA("Active Headcount", PT_Helper!$A$1)
  Font: 32px, white, bold
  Below the number cell (B6:D7, merged):
    Type: "Total Headcount"
    Font: 10px, #94a3b8 (muted), uppercase

  Repeat for New Hires (green #15803d),
  Turnover Rate (red #dc2626, format as %),
  Avg Tenure (blue #1d4ed8, & " yrs" concatenated)

Charts — paste as linked pictures from PT_Helper:
  In PT_Helper: create chart from PivotTable 2 (Headcount by Dept)
  Format: horizontal bar, remove gridlines, remove legend
  Copy chart → Dashboard sheet → Paste Special → Linked Picture
  Linked pictures update automatically when PivotTable refreshes
  Repeat for hire trend chart (column) and tenure chart (bar)

Turnover table — use GETPIVOTDATA in a styled table:
  Build a manual HTML-style table using cell borders and fills
  Pull values from PivotTable 4 using GETPIVOTDATA per department
  Apply conditional formatting: red >15%, amber 10-15%, green <10%

Slicer setup:
  Click any PivotTable → Insert → Slicer → Department
  PivotTable Analyse → Report Connections → tick ALL five PivotTables
  Format slicer: remove header, 6 columns, custom style matching dashboard colours
  Position below KPI row, spanning full dashboard width

Before sharing the dashboard, verify each of the following:

Data model:
  ✓ All three tables loaded into Power Pivot (Data Model)
  ✓ Both date relationships active (Start Date AND Leave Date to DimDate)
  ✓ No circular relationships or ambiguity warnings in Diagram View

Measures:
  ✓ Total Headcount = Active Headcount + Total Leavers (verify manually)
  ✓ Turnover Rate uses average headcount, not closing headcount
  ✓ Avg Tenure filtered to Active employees only (not Leavers)

PivotTables:
  ✓ All five PivotTables pulling from the Data Model (not worksheet ranges)
  ✓ Hire trend months sorted Jan-Dec, not alphabetically
  ✓ Turnover table shows correct Dept Turnover Rate per row

Dashboard:
  ✓ All four KPI card values match PivotTable 1 directly
  ✓ Slicer Report Connections tick all five PivotTables
  ✓ Selecting "Sales" slicer updates all charts and the turnover table
  ✓ Conditional formatting on turnover table updates when slicer applied
  ✓ PT_Helper sheet hidden before sharing
  ✓ File saved as .xlsx (not .xlsm — no macros in this project)