Number of employees - Formula

[syl-ade]

Hi All, 

I would like to show the number of employees at indicated time yet when filtered by year for example 2022", the formula counts currently employed and people who left as well.

Headcount = CALCULATE(DISTINCTCOUNT(hra_datasource[Index]),
FILTER(VALUES(hra_datasource[start_date]), hra_datasource[start_date]<=MAX('CALENDAR'[Date])),
FILTER(VALUES(hra_datasource[end_date]), OR(hra_datasource[end_date] >=MAX('CALENDAR'[Date]),ISBLANK(hra_datasource[end_date]))))

The dashboard looks like that 

The correct number that should appear is "number of employees at indicated time" - "Leavers". How can I improve my formula to count only currently hired?

[DataNinja777 · Accepted solution]

Hi @syl-ade ,

I've created a simple dummy data to show the sample headcount calculation.  There are multiple ways to calculate headcount, but the important thing is that you create a data model with your employee table and calendar table as disconnected tables.  This is crutial for performing fiexible headcount analysis which frees you from the time dimension constraints.  One observation I made regarding your original formula is that you are using OR instead of &&, and for headcount calcualation, both after the starting date AND before the termination date conditions have to be met, so OR is not appropriate for headcount calculation.  Also, another point is that while the starting date has all the rows with date values, I suppose end_date has many blank values as some of the employees have still not left the company at the point of preparing the headcount analysis.  In the long run, everyone leaves the company but it is the fact that employee table contains blank values for end date for current employees. 

In order to deal with blank values in the end_date field, you can create a calculated column to say that if it is blank, certain date in the future should be filled in, such as shown below.  Let's call it "Adjusted end date" for this example.  

I've tweaked your original formula to produce the headcount analysis like below:

There are multiple ways to produce the same output, and another way is to use sumx formula like below:

There's an article which discusses headcount calculation in the link below, but I guess you are on the right track, with respect to the use of disconnected tables, and just needed to fix "OR" part to "&&" and then also, needed to deal with blank values in your end_date field as typically, employee tables end date fields have blank values for current employees. 

https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

I attach an example pbix file with the fake data I created mimicking your table name.  

Best regards,

[lbendlin]

Headcount =
CALCULATE (
    DISTINCTCOUNT ( hra_datasource[Index] ),
    hra_datasource[start_date] <= MIN ( 'CALENDAR'[Date] ),
    COALESCE ( hra_datasource[end_date], TODAY () ) >= MAX ( 'CALENDAR'[Date] )
)

[syl-ade]

Hi Ibendlin, 

The code does not seem to work well unfortunately.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[syl-ade]

Sample data:

first_name	surname	gender	employment_type	company	business unit	N+2	start_date	end_date
John	Smith	Male	B2B	Company A	BUIS	SIS (Sales IS)	2007-09-01
Emily	Johnson	Male	B2B	Company A	BUCL	Delivery Modern Work&Security	2009-02-12
Michael	Williams	Male	B2B	Company A	BUCL	Sales Modern Work&Security (SMS)	2009-02-12
Sarah	Brown	Male	FTE	Company A	BUMS	DMS (Delivery MS)	2022-10-10	2023-07-31
James	Jones	Female	FTE	Company B	BUCS	SNS (Sales NS)	2022-11-02	2023-04-15
Emma	Davis	Male	FTE	Company B	BUST	PRM	2022-12-01	2023-02-28
Jacob	Brendon	Male	CA	Company A	BUCL	Hybrid Cloud & Data & AI	2022-12-01	2024-03-31

In the model there are two 1-to-many inactive relationships:

1. date 1---* start_date

2. date 1---* end_date

[lbendlin]

see attached

[syl-ade]

It's not possible to put the HC calculation on the Card Visualisation. 

[lbendlin]

Which number do you expect to show?