Retained Employees with TenureDate table

[LaBicicleta]

Hi, I'm looking to beef up a report counting EmployeesAtDate and EmployeesAt1YearPrior with a retained employees since 1YearAgo column. So far my Excel PowerPivot PivotTable is:

Row LabelsEmployeesAtDateEmployeesAt1YearPrior

1/1/2019	189
2/1/2019	199
3/1/2019	203
4/1/2019	207
5/1/2019	217
6/1/2019	225
7/1/2019	227
8/1/2019	237
9/1/2019	243
10/1/2019	247
11/1/2019	239
12/1/2019	234
1/1/2020	231	189
2/1/2020	235	199
3/1/2020	228	203
4/1/2020	227	207
5/1/2020	236	217
6/1/2020	233	225
7/1/2020	233	227
8/1/2020	221	237
9/1/2020	223	243
10/1/2020	215	247
11/1/2020	218	239
12/1/2020	213	234
1/1/2021	209	231
2/1/2021	200	235
3/1/2021	204	228
4/1/2021	201	227
5/1/2021	199	236
6/1/2021	185	233
7/1/2021	187	233
8/1/2021	194	221

There's two pretty straightforward tables involved here.

tblCalendar = {"1/1/2019"..."8/1/2021"} - the first date of each month in the period of interest

qryEmployeeActiveTime = a set of rows made up of an employee ID and a date for every date they are active. For instance, if Alice was active from 1/1/2020 thru 1/3/2020, there would be the following rows in the table:

Employee	TenureDate
Alice	1/1/2020
Alice	1/2/2020
Alice	1/3/2020

In the PivotTable on the top, "EmployeesAtDate" is a count of all rows where TenureDate matches the tblCalendar[Date] of the row given. The "EmployeesAt1YearPrior" is a count of all rows where the TenureDate matches DATEADD(tblCalendar[Date],-1,YEAR) of the row given.

What I need to do for the retained employees is get a count of all employees who are active at the tblCalendar[Date] of the row given, who were also active at DATEADD(tblCalendar[Date],-1,YEAR). I'm throwing crap at the wall to see what sticks and not coming up with much. Any pointers?

[amitchandak]

@LaBicicleta , What is you source data, where we can see Alice's start and end date.

If you have a start and end date. Refer to the file attached, which can help

[LaBicicleta]

Hi @amitchandak , thanks for reaching out!

The issue is that we have a lot of rehires here, so there's multiple periods of tenure that need considered, I can't just grab the current "End Date" from the employee table if they're a rehire.

However, what I can do is use PowerQuery to generate a table that would have one row for each employee-tenure period, a la:

Row ID	Employee ID	Tenure_Start	Tenure_End
1	Alice	3/1/2018	8/20/2019
2	Alice	10/31/2019	12/31/2049
3	Bob	7/1/2021	12/31/2049

I can certainly try this out.

I did try to pursue a solution with my existing two tables in the original post, and I came up with this English explanation of what I envision as the solution:

1. Filter for all rows where the [TenureDate] is tblCalendar[Date] or DATEADD(tblCalendar[Date],-1,YEAR).
2. Summarize this table and group by [Employee ID], counting [EmployeeID] instances as "TenuredInstancesOfInterest".
3. Filter this resulting table for where count of the virtual table is 2.
4. Count rows of this resulting-resulting table.

My attempted solution was this:

=COUNTROWS(
   FILTER(
      SUMMARIZE(
         FILTER(qryEmployeeActiveTime,
            OR(
               [TenureDate]=DATEADD(tblCalendar[Date],0,YEAR), 
               [TenureDate]=DATEADD(tblCalendar[Date],-1,YEAR)
            )
         ),
        [EmployeeID],
        "TenureInstancesOfInterest",COUNT([EmployeeID])
     ),
     [TenureInstancesOfInterest]=2
   )
)