Calculated Column to categorize employee between Hire and Term Date with Blanks in Term Date

[PauloRicardo]

Hi everyone,

I'm struggling categorizing our employees.
The trouble is: I'm trying to add a column where,
when the employee has less than 45 days in the company, hes passing through his "First Experience",
when he has between 45 and 90 days, hes in his "Second Experience",
after that, he's considered "Hired", BUT, if he get fired after the 90 days, he's considered "Fired after experience".

NEW RECRUITS =

VAR PERIOD = DATEDIFF(RH_FICHA_BASICA[HIRE_DATE], [TERM_DATE] +1,DAY)

RETURN

SWITCH(

    TRUE(),

    AND(PERIOD > 0, PERIOD <= 45), "FIRST EXPERIENCE",

    AND(PERIOD>= 46, PERIOD <= 90), "SECOND EXPERIENCE",

    AND(PERIOD > 90, NOT(ISBLANK(RH_FICHA_BASICA[TERM_DATE]) = TRUE())), "FIRED AFTER EXPERIENCE",

    "HIRED")

Problem is, my measure only categorize those employees who has a TERM_DATE, those who's TERM_DATE is BLANK it doesn't categorize correctly. How can i fix this problem?

This is how my calculated column returns

HIRE_DATE	TERM_DATE	NEW RECRUITS
01/01/2024		Hired
22/02/2024		Hired
18/03/2024		Hired
05/03/2024	07/03/2024	First Experience
15/01/2024	08/03/2024	Second Experience
01/12/2023	08/03/2024	Fired After Experience

And this is how i want it to appear

HIRE_DATE	TERM_DATE	NEW RECRUITS
01/01/2024		First Experience
22/02/2024		Second Experience
18/03/2024		Hired
05/03/2024	07/03/2024	First Experience
15/01/2024	08/03/2024	Second Experience
01/12/2023	08/03/2024	Fired After Experience

Pls, Help ;(

[DataNinja777]

Hi @PauloRicardo ,

May I ask you when you are assuming the calculated column reference day should be?  The days passed changes every day by an increment of 1, and I've noted that some of your termination dates are in the future dates, and therefore, I am not sure which date should be used as a reference day, like today().  Do you decide who to terminate 4 months in advance?  

Best regards,