Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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,
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,
Headcount =
CALCULATE (
DISTINCTCOUNT ( hra_datasource[Index] ),
hra_datasource[start_date] <= MIN ( 'CALENDAR'[Date] ),
COALESCE ( hra_datasource[end_date], TODAY () ) >= MAX ( 'CALENDAR'[Date] )
)
Hi Ibendlin,
The code does not seem to work well unfortunately.
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...
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
It's not possible to put the HC calculation on the Card Visualisation.
Which number do you expect to show?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
15 | |
15 |
User | Count |
---|---|
33 | |
26 | |
20 | |
19 | |
17 |