Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
syasmin25
Helper V
Helper V

Number of Employees at a particular Branch overtime

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:

EmpIDEnter DateLeave DateYearBranch Name
101Wednesday, August 15, 2018Thursday, January 3, 20192018Doe
102Wednesday, August 15, 2018Thursday, January 3, 20192018Doe
103Wednesday, August 15, 2018Thursday, January 3, 20192018Doe
104Friday, August 17, 2018Thursday, January 3, 20192018Doe
105Wednesday, August 15, 2018Thursday, January 3, 20192018Doe
101Monday, January 6, 2020Monday, March 23, 20202019Doe
102Wednesday, October 23, 2019Monday, March 23, 20202019Doe
104Monday, January 6, 2020Monday, March 23, 20202019Doe
103Thursday, August 15, 2019Monday, March 23, 20202019Doe
105Thursday, August 15, 2019 2019Doe

 


 

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. 

Number of Employees =
CALCULATE(COUNTROWS(tEmployee),
FILTER(VALUES(tEmployee[Enter Date]),tEmployee[Enter Date] <= MAX(tDates[Dates])),
FILTER(VALUES(tEmployee[Leave Date]), OR(tEmployee[Leave Date] >= MIN(tDates[Dates]),ISBLANK(tEmployee[Leave Date]))))

However, my numbers seems incorrect.t_Calendar.PNG

 
Whereas, I have only had one employee in the  branch during that time.
tEmp.PNG

 



I am trying to make this as dynamic as possible. However, I guess I am having trouble trying to figure out what I am doing wrong here. 
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.






7 REPLIES 7
syasmin25
Helper V
Helper V

Date Table:

DatesQTRYear
8/14/2018Q12018
8/19/2018Q12018
8/20/2018Q12018
8/21/2018Q12018
8/22/2018Q12018
8/23/2018Q12018
8/24/2018Q12018
8/25/2018Q12018
8/26/2018Q12018
9/1/2018Q12018
9/2/2018Q12018
9/3/2018Q12018
9/4/2018Q12018
9/5/2018Q12018
9/6/2018Q12018
9/7/2018Q12018
9/8/2018Q12018
9/9/2018Q12018
9/10/2018Q12018
9/11/2018Q12018
9/12/2018Q12018
9/13/2018Q12018
9/14/2018Q12018
9/15/2018Q12018
9/16/2018Q12018
9/17/2018Q12018
9/18/2018Q12018
9/19/2018Q12018
9/20/2018Q12018
9/21/2018Q12018
9/22/2018Q12018
9/23/2018Q12018
9/24/2018Q12018
9/25/2018Q12018
9/26/2018Q12018
9/27/2018Q12018
9/28/2018Q12018
9/29/2018Q12018
9/30/2018Q12018
10/1/2018Q12018
10/2/2018Q12018
10/3/2018Q12018
10/4/2018Q12018
10/5/2018Q12018
10/6/2018Q12018
10/7/2018Q12018
10/8/2018Q12018
10/9/2018Q12018
10/10/2018Q12018
10/11/2018Q12018
10/12/2018Q12018
10/13/2018Q12018
10/14/2018Q12018
10/15/2018Q12018
10/16/2018Q12018
10/17/2018Q12018
10/18/2018Q12018
10/23/2018Q22018
10/24/2018Q22018
10/25/2018Q22018
10/26/2018Q22018
10/27/2018Q22018
10/28/2018Q22018
10/29/2018Q22018
10/30/2018Q22018
10/31/2018Q22018
11/1/2018Q22018
11/2/2018Q22018
11/3/2018Q22018
11/4/2018Q22018
11/5/2018Q22018
11/6/2018Q22018
11/7/2018Q22018
11/8/2018Q22018
11/9/2018Q22018
11/10/2018Q22018
11/11/2018Q22018
11/12/2018Q22018
11/13/2018Q22018
11/14/2018Q22018
11/15/2018Q22018
11/16/2018Q22018
11/17/2018Q22018
11/18/2018Q22018
11/19/2018Q22018
11/20/2018Q22018
11/21/2018Q22018
11/22/2018Q22018
11/23/2018Q22018
11/24/2018Q22018
11/25/2018Q22018
11/26/2018Q22018
11/27/2018Q22018
11/28/2018Q22018
11/29/2018Q22018
11/30/2018Q22018
12/1/2018Q22018
12/2/2018Q22018
12/3/2018Q22018
12/4/2018Q22018
12/5/2018Q22018
12/6/2018Q22018
12/7/2018Q22018
12/8/2018Q22018
12/9/2018Q22018
12/10/2018Q22018
12/11/2018Q22018
12/12/2018Q22018
12/13/2018Q22018
12/14/2018Q22018
12/15/2018Q22018
12/16/2018Q22018
12/17/2018Q22018
12/18/2018Q22018
8/15/2019Q12019
8/15/2019Q12019
8/16/2019Q12019
8/17/2019Q12019
8/18/2019Q12019
8/19/2019Q12019
8/20/2019Q12019
8/21/2019Q12019
8/22/2019Q12019
8/23/2019Q12019
8/24/2019Q12019
8/25/2019Q12019
8/26/2019Q12019
8/27/2019Q12019
8/28/2019Q12019
8/29/2019Q12019
8/30/2019Q12019
8/31/2019Q12019
9/1/2019Q12019
9/2/2019Q12019
9/3/2019Q12019
9/4/2019Q12019
9/5/2019Q12019
9/6/2019Q12019
9/7/2019Q12019
9/8/2019Q12019
9/9/2019Q12019
9/10/2019Q12019
9/11/2019Q12019
9/12/2019Q12019
9/13/2019Q12019
9/14/2019Q12019
9/15/2019Q12019
9/16/2019Q12019
9/17/2019Q12019

DatesQTRYEAR
9/18/2019Q12019
9/19/2019Q12019
9/20/2019Q12019
9/21/2019Q12019
9/22/2019Q12019
9/23/2019Q12019
9/24/2019Q12019
9/25/2019Q12019
9/26/2019Q12019
9/27/2019Q12019
9/28/2019Q12019
9/29/2019Q12019
9/30/2019Q12019
10/1/2019Q12019
10/2/2019Q12019
10/3/2019Q12019
10/4/2019Q12019
10/5/2019Q12019
10/6/2019Q12019
10/7/2019Q12019
10/8/2019Q12019
10/9/2019Q12019
10/10/2019Q12019
10/11/2019Q12019
10/12/2019Q12019
10/13/2019Q12019
10/14/2019Q12019
10/15/2019Q12019
10/16/2019Q12019
10/17/2019Q12019
10/22/2019Q22019
10/23/2019Q22019
10/24/2019Q22019
10/25/2019Q22019
10/26/2019Q22019
10/27/2019Q22019
10/28/2019Q22019
10/29/2019Q22019
10/30/2019Q22019
10/31/2019Q22019
11/1/2019Q22019
11/2/2019Q22019
11/4/2019Q22019
11/4/2019Q22019
11/5/2019Q22019
11/6/2019Q22019
11/7/2019Q22019
11/8/2019Q22019
11/9/2019Q22019
11/10/2019Q22019
11/11/2019Q22019
11/12/2019Q22019
11/13/2019Q22019
11/14/2019Q22019
11/15/2019Q22019
11/16/2019Q22019
11/17/2019Q22019
11/18/2019Q22019
11/19/2019Q22019
11/20/2019Q22019
11/21/2019Q22019
11/22/2019Q22019
11/23/2019Q22019
11/24/2019Q22019
11/25/2019Q22019
11/26/2019Q22019
11/27/2019Q22019
11/28/2019Q22019
11/29/2019Q22019
11/30/2019Q22019
12/1/2019Q22019
12/2/2019Q22019
12/3/2019Q22019
12/4/2019Q22019
12/5/2019Q22019
12/6/2019Q22019
12/7/2019Q22019
12/8/2019Q22019
12/9/2019Q22019
12/10/2019Q22019
12/11/2019Q22019
12/12/2019Q22019
12/13/2019Q22019
12/14/2019Q22019
12/15/2019Q22019
12/16/2019Q22019
12/17/2019Q22019
12/18/2019Q22019
12/19/2019Q22019
1/6/2020Q32019
1/7/2020Q32019
1/8/2020Q32019
1/9/2020Q32019
1/10/2020Q32019
1/11/2020Q32019
1/12/2020Q32019
1/13/2020Q32019
1/14/2020Q32019
1/15/2020Q32019
1/16/2020Q32019
1/17/2020Q32019
1/18/2020Q32019
1/19/2020Q32019
1/20/2020Q32019
1/21/2020Q32019
1/22/2020Q32019
1/23/2020Q32019
1/24/2020Q32019
1/25/2020Q32019
1/26/2020Q32019
1/27/2020Q32019
1/28/2020Q32019
1/29/2020Q32019
1/30/2020Q32019
1/31/2020Q32019
2/1/2020Q32019
2/2/2020Q32019
2/3/2020Q32019
2/4/2020Q32019
2/5/2020Q32019
2/6/2020Q32019
2/7/2020Q32019
2/8/2020Q32019
2/9/2020Q32019
2/10/2020Q32019
2/11/2020Q32019
2/12/2020Q32019
2/13/2020Q32019
2/14/2020Q32019
2/15/2020Q32019
2/16/2020Q32019
2/17/2020Q32019
2/18/2020Q32019
2/19/2020Q32019
2/20/2020Q32019
2/21/2020Q32019
2/22/2020Q32019
2/23/2020Q32019
2/24/2020Q32019
2/25/2020Q32019
2/26/2020Q32019
2/27/2020Q32019
2/28/2020Q32019
2/29/2020Q32019
3/1/2020Q32019
3/2/2020Q32019
3/3/2020Q32019
3/4/2020Q32019
3/5/2020Q32019
3/6/2020Q32019
3/7/2020Q32019
3/8/2020Q32019
3/9/2020Q32019
3/10/2020Q32019
3/11/2020Q32019
3/12/2020Q32019
3/23/2020Q42019


** Date Table continued

Thank you so much for that. But, when I filter to Quarter 1 for 2018, it shows blank. The data shows 3 employees during Q1.

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. 

 

emp.jpg

stevedep
Memorable Member
Memorable Member

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:

employees.gif

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

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.