cancel
Showing results 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

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:

 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.

7 REPLIES 7
Helper V

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
Helper V
 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

Memorable Member

Does my solution work for you?

Proud to be a Super User!

Helper V

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.

Memorable Member

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.

Proud to be a Super User!

Memorable Member

Please let us know if this works for you.

Proud to be a Super User!

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:

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.

Proud to be a Super User!