topic Re: Number of Employees at a particular Branch overtime in DAX Commands and Tips

Number of Employees at a particular Branch overtime

syasmin25 — Tue, 09 Jun 2020 12:50:25 GMT

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.

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.

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

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.

Re: Number of Employees at a particular Branch overtime

stevedep — Tue, 09 Jun 2020 08:17:00 GMT

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.

Re: Number of Employees at a particular Branch overtime

syasmin25 — Tue, 09 Jun 2020 12:59:03 GMT

Date Table:

Dates	QTR	Year
8/14/2018	Q1	2018
8/19/2018	Q1	2018
8/20/2018	Q1	2018
8/21/2018	Q1	2018
8/22/2018	Q1	2018
8/23/2018	Q1	2018
8/24/2018	Q1	2018
8/25/2018	Q1	2018
8/26/2018	Q1	2018
9/1/2018	Q1	2018
9/2/2018	Q1	2018
9/3/2018	Q1	2018
9/4/2018	Q1	2018
9/5/2018	Q1	2018
9/6/2018	Q1	2018
9/7/2018	Q1	2018
9/8/2018	Q1	2018
9/9/2018	Q1	2018
9/10/2018	Q1	2018
9/11/2018	Q1	2018
9/12/2018	Q1	2018
9/13/2018	Q1	2018
9/14/2018	Q1	2018
9/15/2018	Q1	2018
9/16/2018	Q1	2018
9/17/2018	Q1	2018
9/18/2018	Q1	2018
9/19/2018	Q1	2018
9/20/2018	Q1	2018
9/21/2018	Q1	2018
9/22/2018	Q1	2018
9/23/2018	Q1	2018
9/24/2018	Q1	2018
9/25/2018	Q1	2018
9/26/2018	Q1	2018
9/27/2018	Q1	2018
9/28/2018	Q1	2018
9/29/2018	Q1	2018
9/30/2018	Q1	2018
10/1/2018	Q1	2018
10/2/2018	Q1	2018
10/3/2018	Q1	2018
10/4/2018	Q1	2018
10/5/2018	Q1	2018
10/6/2018	Q1	2018
10/7/2018	Q1	2018
10/8/2018	Q1	2018
10/9/2018	Q1	2018
10/10/2018	Q1	2018
10/11/2018	Q1	2018
10/12/2018	Q1	2018
10/13/2018	Q1	2018
10/14/2018	Q1	2018
10/15/2018	Q1	2018
10/16/2018	Q1	2018
10/17/2018	Q1	2018
10/18/2018	Q1	2018
10/23/2018	Q2	2018
10/24/2018	Q2	2018
10/25/2018	Q2	2018
10/26/2018	Q2	2018
10/27/2018	Q2	2018
10/28/2018	Q2	2018
10/29/2018	Q2	2018
10/30/2018	Q2	2018
10/31/2018	Q2	2018
11/1/2018	Q2	2018
11/2/2018	Q2	2018
11/3/2018	Q2	2018
11/4/2018	Q2	2018
11/5/2018	Q2	2018
11/6/2018	Q2	2018
11/7/2018	Q2	2018
11/8/2018	Q2	2018
11/9/2018	Q2	2018
11/10/2018	Q2	2018
11/11/2018	Q2	2018
11/12/2018	Q2	2018
11/13/2018	Q2	2018
11/14/2018	Q2	2018
11/15/2018	Q2	2018
11/16/2018	Q2	2018
11/17/2018	Q2	2018
11/18/2018	Q2	2018
11/19/2018	Q2	2018
11/20/2018	Q2	2018
11/21/2018	Q2	2018
11/22/2018	Q2	2018
11/23/2018	Q2	2018
11/24/2018	Q2	2018
11/25/2018	Q2	2018
11/26/2018	Q2	2018
11/27/2018	Q2	2018
11/28/2018	Q2	2018
11/29/2018	Q2	2018
11/30/2018	Q2	2018
12/1/2018	Q2	2018
12/2/2018	Q2	2018
12/3/2018	Q2	2018
12/4/2018	Q2	2018
12/5/2018	Q2	2018
12/6/2018	Q2	2018
12/7/2018	Q2	2018
12/8/2018	Q2	2018
12/9/2018	Q2	2018
12/10/2018	Q2	2018
12/11/2018	Q2	2018
12/12/2018	Q2	2018
12/13/2018	Q2	2018
12/14/2018	Q2	2018
12/15/2018	Q2	2018
12/16/2018	Q2	2018
12/17/2018	Q2	2018
12/18/2018	Q2	2018
8/15/2019	Q1	2019
8/15/2019	Q1	2019
8/16/2019	Q1	2019
8/17/2019	Q1	2019
8/18/2019	Q1	2019
8/19/2019	Q1	2019
8/20/2019	Q1	2019
8/21/2019	Q1	2019
8/22/2019	Q1	2019
8/23/2019	Q1	2019
8/24/2019	Q1	2019
8/25/2019	Q1	2019
8/26/2019	Q1	2019
8/27/2019	Q1	2019
8/28/2019	Q1	2019
8/29/2019	Q1	2019
8/30/2019	Q1	2019
8/31/2019	Q1	2019
9/1/2019	Q1	2019
9/2/2019	Q1	2019
9/3/2019	Q1	2019
9/4/2019	Q1	2019
9/5/2019	Q1	2019
9/6/2019	Q1	2019
9/7/2019	Q1	2019
9/8/2019	Q1	2019
9/9/2019	Q1	2019
9/10/2019	Q1	2019
9/11/2019	Q1	2019
9/12/2019	Q1	2019
9/13/2019	Q1	2019
9/14/2019	Q1	2019
9/15/2019	Q1	2019
9/16/2019	Q1	2019
9/17/2019	Q1	2019

Re: Number of Employees at a particular Branch overtime

syasmin25 — Tue, 09 Jun 2020 13:00:07 GMT

Dates	QTR	YEAR
9/18/2019	Q1	2019
9/19/2019	Q1	2019
9/20/2019	Q1	2019
9/21/2019	Q1	2019
9/22/2019	Q1	2019
9/23/2019	Q1	2019
9/24/2019	Q1	2019
9/25/2019	Q1	2019
9/26/2019	Q1	2019
9/27/2019	Q1	2019
9/28/2019	Q1	2019
9/29/2019	Q1	2019
9/30/2019	Q1	2019
10/1/2019	Q1	2019
10/2/2019	Q1	2019
10/3/2019	Q1	2019
10/4/2019	Q1	2019
10/5/2019	Q1	2019
10/6/2019	Q1	2019
10/7/2019	Q1	2019
10/8/2019	Q1	2019
10/9/2019	Q1	2019
10/10/2019	Q1	2019
10/11/2019	Q1	2019
10/12/2019	Q1	2019
10/13/2019	Q1	2019
10/14/2019	Q1	2019
10/15/2019	Q1	2019
10/16/2019	Q1	2019
10/17/2019	Q1	2019
10/22/2019	Q2	2019
10/23/2019	Q2	2019
10/24/2019	Q2	2019
10/25/2019	Q2	2019
10/26/2019	Q2	2019
10/27/2019	Q2	2019
10/28/2019	Q2	2019
10/29/2019	Q2	2019
10/30/2019	Q2	2019
10/31/2019	Q2	2019
11/1/2019	Q2	2019
11/2/2019	Q2	2019
11/4/2019	Q2	2019
11/4/2019	Q2	2019
11/5/2019	Q2	2019
11/6/2019	Q2	2019
11/7/2019	Q2	2019
11/8/2019	Q2	2019
11/9/2019	Q2	2019
11/10/2019	Q2	2019
11/11/2019	Q2	2019
11/12/2019	Q2	2019
11/13/2019	Q2	2019
11/14/2019	Q2	2019
11/15/2019	Q2	2019
11/16/2019	Q2	2019
11/17/2019	Q2	2019
11/18/2019	Q2	2019
11/19/2019	Q2	2019
11/20/2019	Q2	2019
11/21/2019	Q2	2019
11/22/2019	Q2	2019
11/23/2019	Q2	2019
11/24/2019	Q2	2019
11/25/2019	Q2	2019
11/26/2019	Q2	2019
11/27/2019	Q2	2019
11/28/2019	Q2	2019
11/29/2019	Q2	2019
11/30/2019	Q2	2019
12/1/2019	Q2	2019
12/2/2019	Q2	2019
12/3/2019	Q2	2019
12/4/2019	Q2	2019
12/5/2019	Q2	2019
12/6/2019	Q2	2019
12/7/2019	Q2	2019
12/8/2019	Q2	2019
12/9/2019	Q2	2019
12/10/2019	Q2	2019
12/11/2019	Q2	2019
12/12/2019	Q2	2019
12/13/2019	Q2	2019
12/14/2019	Q2	2019
12/15/2019	Q2	2019
12/16/2019	Q2	2019
12/17/2019	Q2	2019
12/18/2019	Q2	2019
12/19/2019	Q2	2019
1/6/2020	Q3	2019
1/7/2020	Q3	2019
1/8/2020	Q3	2019
1/9/2020	Q3	2019
1/10/2020	Q3	2019
1/11/2020	Q3	2019
1/12/2020	Q3	2019
1/13/2020	Q3	2019
1/14/2020	Q3	2019
1/15/2020	Q3	2019
1/16/2020	Q3	2019
1/17/2020	Q3	2019
1/18/2020	Q3	2019
1/19/2020	Q3	2019
1/20/2020	Q3	2019
1/21/2020	Q3	2019
1/22/2020	Q3	2019
1/23/2020	Q3	2019
1/24/2020	Q3	2019
1/25/2020	Q3	2019
1/26/2020	Q3	2019
1/27/2020	Q3	2019
1/28/2020	Q3	2019
1/29/2020	Q3	2019
1/30/2020	Q3	2019
1/31/2020	Q3	2019
2/1/2020	Q3	2019
2/2/2020	Q3	2019
2/3/2020	Q3	2019
2/4/2020	Q3	2019
2/5/2020	Q3	2019
2/6/2020	Q3	2019
2/7/2020	Q3	2019
2/8/2020	Q3	2019
2/9/2020	Q3	2019
2/10/2020	Q3	2019
2/11/2020	Q3	2019
2/12/2020	Q3	2019
2/13/2020	Q3	2019
2/14/2020	Q3	2019
2/15/2020	Q3	2019
2/16/2020	Q3	2019
2/17/2020	Q3	2019
2/18/2020	Q3	2019
2/19/2020	Q3	2019
2/20/2020	Q3	2019
2/21/2020	Q3	2019
2/22/2020	Q3	2019
2/23/2020	Q3	2019
2/24/2020	Q3	2019
2/25/2020	Q3	2019
2/26/2020	Q3	2019
2/27/2020	Q3	2019
2/28/2020	Q3	2019
2/29/2020	Q3	2019
3/1/2020	Q3	2019
3/2/2020	Q3	2019
3/3/2020	Q3	2019
3/4/2020	Q3	2019
3/5/2020	Q3	2019
3/6/2020	Q3	2019
3/7/2020	Q3	2019
3/8/2020	Q3	2019
3/9/2020	Q3	2019
3/10/2020	Q3	2019
3/11/2020	Q3	2019
3/12/2020	Q3	2019
3/23/2020	Q4	2019

** Date Table continued

Re: Number of Employees at a particular Branch overtime

stevedep — Tue, 09 Jun 2020 13:52:53 GMT

Does my solution work for you?

Re: Number of Employees at a particular Branch overtime

syasmin25 — Wed, 10 Jun 2020 15:32:03 GMT

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.

Re: Number of Employees at a particular Branch overtime

stevedep — Wed, 10 Jun 2020 15:45:46 GMT

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.

Re: Number of Employees at a particular Branch overtime

stevedep — Fri, 12 Jun 2020 07:12:52 GMT

Please let us know if this works for you.