Retained Employees with TenureDate table

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:

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
   )
)