Calculate Compensation End Dates of an employee with multiple Compensation Start Dates

[Anonymous]

Hi all,

I need your help creating a correct Compensation End date for my data. 

Employee Id	Employee Name	Compensation Start Date
100010	David Mayer	01/01/2020
100010	David Mayer	12/14/2020
100010	David Mayer	02/01/2021
100010	David Mayer	07/01/2021
100290	Jessi Park	06/08/2020
100290	Jessi Park	05/01/2021

I created a calculated column:

Compensation End Date =

VAR maxdate =

CALCULATE (

MAX (Compensation[Compensation_Start_Date__c] ),

FILTER (

Compensation,

Compensation[Employee__r.Id] = EARLIER(Compensation[Employee__r.Id])

)

)

RETURN

IF ( Compensation[Compensation_Start_Date__c] = maxdate, BLANK(), maxdate - 1 )

Result: 

Employee Id	Employee Name	Compensation Start Date	Compensation End Date	Expected Compensation End Date
100010	David Mayer	01/01/2020	06/302021	12/13/2020
100010	David Mayer	12/14/2020	06/302021	01/31/2020
100010	David Mayer	02/01/2021	06/30/2021	06/30/2021
100010	David Mayer	07/01/2021
100290	Jessi Park	06/08/2020	04/30/2021	04/30/2021
100290	Jessi Park	05/01/2021

The formula is doing fine for records with only 2 rows, however, it is not giving me the desired result for employees with >2 records. How can I tweak my formula to return the desired result?  Thank you in advance for your help.

Regards,

Kat

[Jos_Woolley]

You're very welcome!

Cheers

[Jos_Woolley · Accepted solution]

Hi,

Perhaps this:

Compensation End Date =
VAR NextDate =
    CALCULATE (
        MIN ( Compensation[Compensation Start Date] ),
        FILTER (
            Compensation,
            Compensation[Employee Id] = EARLIER ( Compensation[Employee Id] )
                && Compensation[Compensation Start Date]
                    > EARLIER ( Compensation[Compensation Start Date] )
        )
    )
RETURN
    IF ( NextDate = 0, BLANK (), NextDate - 1 )

Regards

[Anonymous]

@Jos_Woolley It worked perfectly!! Thank you so so much for taking the time to solve my problem! 😊