Identifying if a Record has ended using start and end dates of table

[Anonymous]

Hi There,

I'm new to PowerBi and am trying to identify if an employee has left the organization vs those that have transferred roles. My table contains the employee ID, job code, start date for that role and end date for that role where the end date is blank if they are a current employee.

I would like to use Dax to add an additional column to label all terminated employees - these are employees no longer with the company or have left and returned more than 14 days after the end date of the role..  I have attached table of with a sample of data.  Where 'expected Values' si what I'm hopgin to use my Dax formula with.

In my example,Employee 1 has transferred to a new role effective April 5th(end date of previous role was on Friday and they start the new role on Monday).  So even though Line 1 has an end date, I do not want them to be labelled as terminated.  However,  Because employee 2 ended on March 31, 2021 and came back the next year, they should be considered terminated effective march 31, 2021.  (The buffer period between the end date of one line and start date of another line for the same employee should be 14 days).  

Employee ID	Job Code	Start Date	End Date	Expected Values
1	A	1/1/2021	4/2/2021
1	B	4/5/2021
2	B	2/2/2021	3/31/2021	Left Organization
2	B	1/1/2022
3	A	2/1/2021	3/31/2022	Left Organization
4	A	2/2/2022

Hoping Someone will be able to help me.  Thanks in Advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to try this code to create a calculated column.

Flag =
VAR _COUNT =
    CALCULATE (
        COUNT ( Employee[Employee ID] ),
        ALLEXCEPT ( Employee, Employee[Employee ID] )
    )
VAR _NEXTSTARTDATE =
    CALCULATE (
        MIN ( Employee[Start Date] ),
        FILTER (
            ALLEXCEPT ( Employee, Employee[Employee ID] ),
            Employee[Start Date] > EARLIER ( Employee[End Date] )
        )
    )
VAR _DATEDIFF =
    DATEDIFF ( Employee[End Date], _NEXTSTARTDATE, DAY )
RETURN
    IF (
        _COUNT = 1,
        IF ( Employee[End Date] = BLANK (), BLANK (), "Left organization" ),
        IF ( _DATEDIFF < 14, BLANK (), "Left organization" )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Anonymous!  I jsut tested this and it does what I needed!

[johnt75]

You can try

Left company =
VAR currentEmployeeID = 'Employee'[Employee ID]
VAR currentEndDate = 'Employee'[End Date]
VAR futureJobs =
    FILTER (
        'Employee',
        'Employee'[ID] = currentEmployeeID
            && 'Employee'[Start Date] <= currentEndDate + 14
    )
RETURN
    IF (
        NOT ( ISBLANK ( currentEndDate ) ) && ISEMPTY ( futureJobs ),
        "Left organization"
    )