Calculating employee headcount by month or year

Question

I have 2 tables

1 table is date table with following fields: date, month, quarter, year, yearmonth,yearnumber

2nd table is Employee table with following fields: EmpID, Hired Date, Termination Date, employees that are active have a blank termination date.

I am trying to do employee head count by month and year. Wrote a dax measure using forums but it is showing me blank results:

Would be gratefull for any help here.

HeadCount =

var endperiod=MAX('date'[Date])

var startperiod=MIN('date'[Date])

return

CALCULATE(DISTINCTCOUNT('shared User'[EmployeeId]),FILTER(ALL('shared User'),('shared User'[HireDate]<=endperiod && 'shared User'[TerminationDate]>=startperiod)))

William Rodriguez William Rodriguez · Accepted Answer · 2019-07-25T17:30:46

You can use the following to accomplish your task (I am assuming you are slicing based on your calendar[date] column, and employee_ID is unique):

EmployeeCount = 
Var __SDate = MIN( 'Calendar'[Date] )
VAR __EDate = MAX ( 'Calendar'[Date] )
REturn 
COUNTX(
    FILTER(
        EmployeeTable,
        EmployeeTable[HIRE_DATE] >= __SDate
        && EmployeeTable[END_DATE] <= __EDate
    ) , [EM_ID]
)

Calculating employee headcount by month or year

1 Answers