Hello,
I am trying to count the number of employees at a certain branch over different periods of time. I am providing a sample below.
employee table:
| EmpID | Enter Date | Leave Date | Year | Branch Name |
| 101 | Wednesday, August 15, 2018 | Thursday, January 3, 2019 | 2018 | Doe |
| 102 | Wednesday, August 15, 2018 | Thursday, January 3, 2019 | 2018 | Doe |
| 103 | Wednesday, August 15, 2018 | Thursday, January 3, 2019 | 2018 | Doe |
| 104 | Friday, August 17, 2018 | Thursday, January 3, 2019 | 2018 | Doe |
| 105 | Wednesday, August 15, 2018 | Thursday, January 3, 2019 | 2018 | Doe |
| 101 | Monday, January 6, 2020 | Monday, March 23, 2020 | 2019 | Doe |
| 102 | Wednesday, October 23, 2019 | Monday, March 23, 2020 | 2019 | Doe |
| 104 | Monday, January 6, 2020 | Monday, March 23, 2020 | 2019 | Doe |
| 103 | Thursday, August 15, 2019 | Monday, March 23, 2020 | 2019 | Doe |
| 105 | Thursday, August 15, 2019 | 2019 | Doe |
I have used the formula below to count the number of employees at the "Doe" branch at certain periods of time and created inactive relationships between the two tables. I am using the "Year" and "QTR" columns from the date column as filters.
However, my numbers seems incorrect.
Whereas, I have only had one employee in the branch during that time.
Any help or even suggestions would be appreciated. I have an employee table that has an enter date and a leave date. And the employee can come back and work on the same branch more than once within the same year. I will be providing a date slicer to show the number of employees at any certain point. While brainstorming, I thought of creating a seperate date table, but it doesn't seem like it is going accordingly. I am still pretty new when it comes to Date calculations so any help would be really greatful.
This is accurate because, in this dataset, there are not employees before the 3rd quarter of 2018, see detailed rows. The first one joined august 15th. The formula works as expected when looking at the detailed rows.
Here you go:
_CountEmployees =
var __MinDate = MIN('date'[Dates])
var __MaxDate = MAX('date'[Dates])
return
CALCULATE(DISTINCTCOUNT(Employee[EmpID]);FILTER(ALL(Employee);Employee[Enter Date]<=__MinDate && Employee[Leave Date]>=__MaxDate))
As seen here:
Link to Power BI file here.
p.s. all the employees left before the end of Q1 2019, because march 23rd is not beyond the boundary of Q1. Therefore the number of employees should be empty, as you can see in my file.
Please mark as solution if this works for you. Thumbs up for the effort is appreciated.
Kind regards, Steve.
Helpful resources
Power BI September 2023 Update
Take a look at the September 2023 Power BI update to learn more.
