Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PauloRicardo
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_DATETERM_DATENEW RECRUITS
01/01/2024  Hired
22/02/2024 Hired
18/03/2024 Hired
05/03/202407/03/2024 First Experience
15/01/2024 08/03/2024Second Experience
01/12/2023 08/03/2024Fired After Experience


And this is how i want it to appear

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

1 REPLY 1
DataNinja777
Super User
Super User

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?  

DataNinja777_0-1713689621887.png

DataNinja777_1-1713690237854.png

 

Best regards,

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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