# of People Employed with specific tenure

[jcastr02]

Hello I have a running list of employees with their names, Employee ID, Hire Date and Term Date.  List would continously grow as we hire employees.   I am trying to find a way to know on a given date how many people are active in the company with specific tenure (essentially removing team members with term dates on them).  Example on September 1st, we had 50 people that were of had a tenure of 31-37 days, 15 of a tenure of 61-67 days, etc.  Below see example of my data & desired results.  (Below are not true values are just made up data)

Below example of my data:

Employee Name	Employee ID	Hire Date	Term Date
Coto, Chris	123	7/23/2018
Peyton, MeLisa	456	1/5/1990
Contreras, Michelle	789	12/28/1995
Belvin, Debra	1122	9/13/1999
Rivers, Carol	1455	8/30/1999
Hepp, Brenda	1788	1/29/1997
Monroe, Natalie	2121	11/8/2013
Hubbard, Tia	2454	5/23/2022	5/23/2022
Gloria, Rachel	2787	5/30/2022	6/10/2022
Bentz-Bailey, Mackenzie	3120	12/27/2021	3/16/2022
Burnes, Mayrn	3453	8/9/2021	3/4/2022
Bademi, Mercedes	3786	12/27/2021	3/16/2022
Wolfer, Adrian	4119	9/6/2021	3/24/2022
Medina, Karla	4452	1/3/2022	3/31/2022
Billig, Dana	4785	4/18/2022	4/25/2022
Mitchell, Taylor	5118	2/28/2022	5/4/2022
Barriga, Kyra	5451	3/7/2022	4/11/2022
Doughty, Justin	5784	3/7/2022	3/11/2022
Salazar, Idan	6117	3/7/2022	3/11/2022
Peterson, Brianna	6450	3/7/2022	3/7/2022
Vera, Amanda	6783	3/7/2022	3/7/2022
Gutierrez, Rocio	7116	4/18/2022	5/16/2022
Mcgee, Michelle	7449	3/28/2022	6/21/2022
Thornton, Mateen	7782	2/7/2022	3/7/2022
Miller, Joanne	8115	3/21/2022	3/30/2022
Russell, Mackenzie	8448	3/14/2022	3/14/2022

Desired Result

	# of People Employed Tenure Days 31-37	# of People Employed Tenure Days 61-67	# of People Employed Tenure Days 91-97	# of People Employed Tenure Days 181-187
9/1/2022	50	15	12	14
9/2/2022	50	15	12	14
9/3/2022	50	15	12	14
9/4/2022	50	15	12	14
9/5/2022	50	15	12	14
9/6/2022	50	15	12	14
9/7/2022	50	15	12	14
9/8/2022	45	12	18	20
9/9/2022	45	12	18	20
9/10/2022	45	12	18	20
9/11/2022	45	12	18	20
9/12/2022	45	12	18	20
9/13/2022	45	12	18	20
9/14/2022	45	12	18	20
9/15/2022	45	12	18	20
9/16/2022	25	16	17	10
9/17/2022	25	16	17	10
9/18/2022	25	16	17	10
9/19/2022	25	16	17	10
9/20/2022	25	16	17	10
9/21/2022	25	16	17	10
9/22/2022	25	16	17	10
9/23/2022	10	5	10	4
9/24/2022	10	5	10	4
9/25/2022	10	5	10	4
9/26/2022	10	5	10	4

[jdbuchanan71 · Accepted solution]

@jcastr02 

Interesting one, I think I got it to work.  I have a Dates table and a Tenure table along with the employee table.

Here is the measure I ended up with:

Active Employee Count = 
VAR _MinDate = MIN ( Dates[Date] )
VAR _MaxDate = MAX ( Dates[Date] )
VAR _MinDays = MIN ( Tenure[Start] )
VAR _MaxDays = MAX ( Tenure[End] )
VAR _Employees = 
    FILTER ( 
        ALLSELECTED ( Employees[Employee ID], Employees[Hire Date], Employees[Term Date] ), 
        Employees[Hire Date] <= _MaxDate && 
        ( Employees[Term Date] >= _MinDate || ISBLANK ( Employees[Term Date] ) ) 
    )
VAR _EmployeesWithDays = 
    ADDCOLUMNS ( _Employees, "@Days", _MaxDate - Employees[Hire Date] )
VAR _EmployeesByDays = 
    FILTER ( _EmployeesWithDays, [@Days] >= _MinDays && [@Days] <= _MaxDays )
RETURN 
    COUNTROWS ( _EmployeesByDays )

You would need to modify the day ranges on the tenure table to match what you are looking for.  I have attached my sample file for you to look at.

[jcastr02]

@jdbuchanan71  You are Amazing - you have no idea how long it was taking me to figure out a solution!  I forgot to mention I have a column for "location" on the employee table which when I try to add a filter on the page, the #'s do not change.  Do you know how I could remedy that?  Your guidance is greatly appreciated.  See below two examples.

[jdbuchanan71 · Accepted solution]

We can include the location in the ALLSELECTED like this:

Active Employee Count = 
VAR _MinDate = MIN ( Dates[Date] )
VAR _MaxDate = MAX ( Dates[Date] )
VAR _MinDays = MIN ( Tenure[Start] )
VAR _MaxDays = MAX ( Tenure[End] )
VAR _Employees = 
    FILTER (
        ALLSELECTED ( Employees[Employee ID], Employees[Hire Date], Employees[Term Date], Employees[Location] ), 
        Employees[Hire Date] <= _MaxDate && 
        Employees[Term Date] >= _MinDate || ISBLANK ( Employees[Term Date] ) 
    )
VAR _EmployeesWithDays = 
    ADDCOLUMNS ( _Employees, "@Days", _MaxDate - Employees[Hire Date] )
VAR _EmployeesByDays = 
    FILTER ( _EmployeesWithDays, [@Days] >= _MinDays && [@Days] <= _MaxDays )
RETURN 
    COUNTROWS ( _EmployeesByDays )

[jdbuchanan71 · Accepted solution]

@jcastr02 

Interesting one, I think I got it to work.  I have a Dates table and a Tenure table along with the employee table.

Here is the measure I ended up with:

Active Employee Count = 
VAR _MinDate = MIN ( Dates[Date] )
VAR _MaxDate = MAX ( Dates[Date] )
VAR _MinDays = MIN ( Tenure[Start] )
VAR _MaxDays = MAX ( Tenure[End] )
VAR _Employees = 
    FILTER ( 
        ALLSELECTED ( Employees[Employee ID], Employees[Hire Date], Employees[Term Date] ), 
        Employees[Hire Date] <= _MaxDate && 
        ( Employees[Term Date] >= _MinDate || ISBLANK ( Employees[Term Date] ) ) 
    )
VAR _EmployeesWithDays = 
    ADDCOLUMNS ( _Employees, "@Days", _MaxDate - Employees[Hire Date] )
VAR _EmployeesByDays = 
    FILTER ( _EmployeesWithDays, [@Days] >= _MinDays && [@Days] <= _MaxDays )
RETURN 
    COUNTROWS ( _EmployeesByDays )

You would need to modify the day ranges on the tenure table to match what you are looking for.  I have attached my sample file for you to look at.