Create "Employment pause" calculated column

[Anonymous]

I have a table with employee names, their company their starting date, and if they are employed at a specific date.

I want to create a new column (As displayed in the right-most column below) showing if the employee took a pause from employment and then continued with the same company.

The table has the following format:

Employee	Сompany	Starting date	Date	Goes on pause this period
William	Amazon	01.01.2020	01.01.2020	No
William	Amazon	01.01.2020	01.02.2020	Yes
William	Amazon	01.08.2020	01.08.2020	No
Rosa	Amazon	01.03.2020	01.03.2020	No
Rosa	eBay	01.08.2020	01.08.2020	No

Today's month = August (8th month), year = 2020.

From the above table, William had taken a break from 01.02.2020 till 01.08.2020 and renewed with the same company on 01.02.2020, so he should have a "Yes" on his last employment month before the break.

Rosa also took a break, but she also changed the company, so that does not count.

As 01.08.2020 is the last known time period, we don't know if the employee will take a break or not, so there is a "No" for this period as well.

Thank you for assistance in advance (:

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Please try the DAX code below.

Column = 
VAR nextStartDate =
    MINX (
        FILTER (
            Employee,
            Employee[Employee] = EARLIER ( Employee[Employee] )
                && Employee[Сompany] = EARLIER ( Employee[Сompany] )
                && Employee[Starting date] >= EARLIER ( Employee[Date] )
        ),
        Employee[Starting date]
    )
RETURN
    IF ( nextStartDate > Employee[Date], "Yes", "No" )

 

Assume that if one doesn't have a break, then the next starting date will equals to the specific date in its previous row.

Best Regards,

Community Support Team _ Jing Zhang

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

[amitchandak]

@Anonymous , can you provide expected output in table format

[Anonymous]

@amitchandak , the expected output is the right-most column ("Goes on pause this period"), sorry for the possible confusion