cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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 ;(

Super User

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,

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors