Help with employees hire count

[n00ne]

Hi All,

I got a problem to calculate how many employees currently working were hired n months ago. Below you can see table example.
EmpID HireDate ReportDate
A 01-Jan-22 31-Jan-22
B 15-Jan-22 31-Jan-22
C 28-Jan-22 31-Jan-22
D 01-Feb-22 28-Feb-22
E 02-Feb-22 28-Feb-22
A 01-Jan-22 30-Apr-22
B 15-Jan-22 30-Apr-22
X 01-Jan-22 30-Apr-22
D 01-Feb-22 30-Apr-22

When we will see at the month reportdate april and I wanna check how many ppl were hired i.e. 3 month back I need to check which EmpID were there in reportdate Jan and was also hired that month.
In that example I should get result of 2 employees, coz only A and B are working in April (reportdate) and were hired in month of Jan. As you can see there are cases like for EmpID "X" were in April appears as hired in Jan, but it was not available in rows with reportdate Jan.
In such case I cannot count it as employee hired in Jan.
Also I have to have the n month flexible (I would have some parameter here for user to tell how many months back to check).
Second example for 2 months back would be resulting in value 1. There are 2 employees hired in February but only 1 is still on the provided list.

I would be much appreciated if you could help me with that task.

Best Regards,
Adrian

[PaulOlding]

Hi @n00ne 

Here's an option for you.  Instead of having a slicer with X number of month ago, this allows the user to select the month they want. 

First off we need 2 date tables with Month-Year columns (and a month-year number to allow sorting).

Now for the measure.  It first of all gets the employees based on the slicer selections.  Then it get the employees for the selected hire month that were in that report month.  Finally, it intersects that to get employees in both lists.

Hire Count = 
VAR _Basic = VALUES('Table'[EmpID])
VAR _SelectedHirePeriodAsReportPeriod = 
CALCULATETABLE(
    VALUES('Table'[EmpID]),
    TREATAS(VALUES('Hire Date'[Hire Month Year]),'Report Date'[Report Month Year]),
    REMOVEFILTERS('Hire Date'),
    REMOVEFILTERS('Report Date')
) 
VAR _Both = INTERSECT(_Basic, _SelectedHirePeriodAsReportPeriod)
RETURN
    COUNTROWS(_Both)

You can have a report with a couple of slicers on the Month-Year columns for the users to select their months

or a matrix to show all results

[n00ne]

Hi,

Looks really neat, but I have already used first solution from daXtreme.

[daXtreme · Accepted solution]

File attached...