Variable table value to be used for filter

[Anonymous]

I want to calculate headcount for the chosen End of Month (Nov-22) in this case. An employee can hae multiple date_to and Date_from records which captures the change of contract. Here is the situation:

For the above sample data

• If an employee joins back within 7 days (buffer period) from his/her last date_to s/he should be counted in the total headcount (for example, B here is neither a termination for November 2022 nor a hire for December 2022, s/he should be present in the total headcount for November 2022 as an employee)
• If the employee joins back after 7 days s/he is tagged as a re-hire ( example C here is a termination for November 2022 and a hire for December 2022, s/he should not be present in the total headcount)
• Date_to can have future dates present
• An employee with no past record is a new hire for the month in which his/her record starts ( D is a Hire for Dec 2022, so should not be in the headcount for November 2022
• F here is a hire for Feb'22 and Oct'22 and a termination for Apr'22 and should be included in the total headcount for Nov'22
• Currently I have the below logic built:
  •  

Total Employees = 

VAR min_snapshot = CALCULATE(MIN(Parameter[End of Month]), ALLSELECTED())
VAR max_snapshot = CALCULATE(MAX(Parameter[End of Month]), ALLSELECTED())
VAR max_snapshot_2 = CALCULATE(MAX(Parameter[End of Month])+7, ALLSELECTED())

RETURN
CALCULATE(
    DISTINCTCOUNT(unitt_hrreport_employee[user_id]),
    UNION(
        CALCULATETABLE(
  VALUES(unitt_hrreport_employee[user_id]),
  (
    ((unitt_hrreport_employee[date_from] > max_snapshot && unitt_hrreport_employee[date_from] < max_snapshot_2)
    && (unitt_hrreport_employee[date_to] > max_snapshot_2 || ISBLANK(unitt_hrreport_employee[date_to]) = TRUE))
    ),
    FILTER(unitt_hrreport_employee, unitt_hrreport_employee[min_date_from_per_user_id] <= max_snapshot)
  ),
  CALCULATETABLE(
        VALUES(unitt_hrreport_employee[user_id]),
     ( 
        (unitt_hrreport_employee[date_from] <= max_snapshot 
        && (unitt_hrreport_employee[date_to] > max_snapshot || ISBLANK(unitt_hrreport_employee   [date_to] ) = TRUE)
        )  
    ))))​

• Te buffer here is a +7 on the end of month i.e for Nov'22 buffer is 7/12/2022 but this should not be the case it should be from the last date_to of an employee (before the select EOMonth) e.g. C should have the buffer date as 25/11/2022 but C is being wrongly counted in the total employee count.

[Anonymous]

@tamerj1 @amitchandak @johnt75 @andhiii079845 @FreemanZ @Greg_Deckler Please help