cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

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

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors