I have many reasons for sharing content online. The main reasons are that I like sharing what I know, but also that I want to document how to complete a task for future reference. My article today ticks both of those boxes.
How Many Employees by Period
I’m calling this article, “How many employees by period”. Staff come and go for different reasons. In some companies, the number of staff can change over time. The principles used in this article can also be used in other instances. There can be staff moving in and out of departments, on and off of projects, etc. The technique can also be used to work out how many staff were on leave, how many off sick, how many tickets were open in a support queue, or any other concept that has a start and end date in a transactional table.
The demo data I am using here will be used to calculate the total number of staff that were employed in any given year, but it works equally well for any other period of time.
Every time I have solved this problem in the past I have started from scratch and had to think through the filtering behaviour needed prior to writing the DAX. This is the last time I will do that :-). Instead, I will now be able to check back here to find the exact filtering behaviour needed.
The Problem Explained
Consider the below table of employees. Note this is a unique list of employees, and there is a start (commenced) and end (departed) date column.
The objective is to find the number of employees in a given year. To understand this more clearly take a look at the green bars added below. The green bars span the time period covering the employment.
As you can see above, In the year 2019, the number of employees is 4.
- 1: Odele commenced in 2017 and is still employed
- 7: Marjory commenced in 2018 and left early in 2022
- 689: Ulrick commenced in 2017 and left in 2020
- 762: Belicia commenced in 2017 and left early in 2019. She was employed in 2019 because she was there for at least 1 day of that year even though she left during that year.
The solution to his problem
It is not possible to solve this problem using relationships. A relationship is designed to push a filter from a table (a calendar table in this case) onto another table. The use of relationships in this case will not work because the requirement is to filter for a range of dates, between the start and end dates.
The solution therefore is to “harvest” the start and end dates from period (year in this case) from the Calendar table, and then use the start and end dates to put filters on the employee table using formula based filters – no relationships needed. I am not going to write out the solution, step by step; you should watch the video for that. But what I have shown below is the final filter logic that is needed as well as the structure of the DAX formula. This will make it easy to refer back here later when you need a quick, correct solution to the problem.
Total Employees = VAR LastDateInPeriod = MAX ( 'Calendar'[Date] ) VAR FirstDateInPeriod = MIN ( 'Calendar'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( Employees ), Employees[Commenced] <= LastDateInPeriod, OR ( Employees[Departed] >= FirstDateInPeriod, ISBLANK ( Employees[Departed] ) ) ) RETURN Result
Here is the video where I walk through and explain how to solve this problem.