Power BI: Employee count by month
Welcome to this post on calculating employee count by month in Power BI.
The starting point is an Employees table with the start and end date of each employee. For employees currently at the company, the end date is empty.
An example is below.
The goal is the obtain a Power BI chart with the employee count by month.
Create a Date table
As always when dealing with dates, the first action is to create a Date table. You can create a Date table by clicking on Modeling, New Table and then entering this DAX formula:
Date = CALENDARAUTO()
This action creates a table with one row for each day.
Once the Date table is available, we create additional columns in this table that will help us in the visualization later. Each column is created by clicking on Modeling, New Column and then entering the corresponding DAX formula.
The formulas are the following:
Year = Year([Date])
Month = FORMAT([Date], "mmmm")
Year Month number = year([Date])&FORMAT(month([Date]),"00")
Year Month = year([Date])&" "&FORMAT([Date],"mmmm")
And below is what the Date table looks like after adding all the columns.
One column is particularly useful, the 'Year Month Number' column, because it allows us to correctly sort the Year Month column. Without it, the Year Month column would be sorted in alphabetical order: April as first month followed by August.
To correctly sort the Year Month column: select it, click on Sort by Column and choose Year Month number. That will do the job.
If you want to learn more about custom sort in Power BI, you can check my post here.
Create a measure to count employees by month
With the Date table ready, we finally add the measure to count employees. Click on New Measure and enter the following code.
Employee Count =
VAR selectedDate = MAX('Date'[Date])
RETURN
SUMX('Employees',
VAR employeeStartDate = [Start Date]
VAR employeeEndDate = [End Date]
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)
)
This is what the 'Employee Count' formula does:
– it creates a variable selectedDate, equal to the last day of the selected month. For January, the selectedDate is the 31st of January.
– runs the SUMX formula, which takes the Employees table and counts the employees working during the selected date. An employee is working in the selected date if its start date is earlier than the selected date, and its end date is later than the selected date.
Create a Power BI chart with employee count
The last step is creating the chart. You can choose Year Month as axis and Employee Count as values.
And we have our visualization!
I hope this post was helpful!
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.
8 Comments
Thank you so much…, I got the numbers correct…
great to hear!
Hi!
The formula and the logic are great and simple.
The only question that I have is about modeling
which column in the employees' table you're connecting to your date table the start date or the end date?
Hi,
thank you for commenting on the article!
There is no connection between the Employees' table and the Date table.
It's all managed via DAX formulas.
Does this help you?
Luca
Hi Luca,
Thanks for all the work you've done, it's been a great help. Do you know if these calculations will work if we create a connection between the dates table and the employees table? That way if we want to see active count by department/region/employee type etc. we could? Also, would that connection work with the turnover rate as well? I think what you'll see a lot of users need is this calculation would for it to be filterable by the employees table fields. HR users are gonna wanna slice and dice these calculations by typical HR fields (mentioned above). Any thoughts/feedback/help would be greatly appreciated.
Cheers
Hi David,
sorry for the late reply. In case you have an urgent question, feel free to send me an email at lc@finance-bi.com.
The Dates table in the examples is used by formula so it should not be linked to the Employees table. Otherwise, the DAX formulas will no longer work.
However, you can create a new Dates table (maybe called 'Dates Filter'), which you can link to the Employees table and use to slice and dice the various calculations.
Does this help you?
Luca
thank you for your post Luca!
I would like to know how to calculate the number of employees at the end of the month, assuming that if an employee leaves on the last day of the month, he is no longer part of the number of employees at the end of the month.
In your example, if a person leaves on Dec 31th 2020, he is part of the end of month workforce for December 2020.
Translated with http://www.DeepL.com/Translator (free version)
Hi Olivier,
You can modify the formula for Employee Count. Instead of employeeEndDate>=selectedDate you can enter employeeEndDate>selectedDate.
This means that if an employee leaves on the last day of the month, he is no longer counted in the number of employees for the month.
Below is the updated formula for your reference.
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>selectedDate, employeeEndDate=BLANK() ),1,0)
I hope this helps you,
Luca