Headcount on a date vs month headcount vs year headcount

[Tamila]

Hello All.

I am having trouble with calculating headcount, and would greatly appreciate your help.

I have two tables:

1. Employees - employee ID, hiring date, termination date

2. Dates

I need to calculate:

Headcount on a date - all hired employees before that date, not considering terminated employees before that date

Headcount for the month = all employees on the first day of the month, plus newly hired, minus terminated for the month

Headcount for the year = average of headcount for the months of that year

Below are my DAX formulas:

Headcount on a date =

VAR selectedDate = MAX('0. Date'[Date])

RETURN

SUMX('3. Employees',

VAR employeeStartDate = '3. Employees'[HiringDate]

VAR employeeEndDate = '3. Employees'[TerminationDate]

RETURN IF(IF(selectedDate<TODAY(),employeeStartDate<= TODAY(),employeeStartDate<=selectedDate) && OR(employeeEndDate>selectedDate, employeeEndDate=BLANK() ),1,0)

)

For some reason, it works perfectly for earlier years, but for the current year would not consider employees hired in February 2023. 

Headcount for the month = 

MAXX(

    KEEPFILTERS(VALUES('0. Data'[Month])),

    CALCULATE([Headcount on a date])

)

This one works ok.

Headcount for the year =

AVERAGEX(VALUES('0. Date'[Year]),[Headcount for the month])

This one would not work(

I pretty much get the same numbers from each measure but this is not what I need.

If anyone can advise how I can handle this issue, that would be great!

Best regards,

Tamila

[amitchandak]

@Tamila , Try if this blog and file can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Tamila]

@amitchandak Thank you very much for your suggestion. Tried the following formula:

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

However, same story - for 2016-2022 correct result, for 2023 it does not count employees hired in February 2023(