max date based on leaved days

[sohailstsindia]

EmployeeLeave
EMP CODE	LEAVE TYPE	Start DATE	END DATE	DAYS
EM0001	ANNUAL LEAVE	1-Jan-16	31-Jan-16	30
EM0001	UNPAID LEAVE	15-Mar-16	20-Mar-16	5
EM0001	UNPAID LEAVE	10-Jun-16	15-Jun-16	5
EM0001	ANNUAL LEAVE	10-Feb-17	31-Mar-17	49
EM0001	UNPAID LEAVE	10-Apr-17	20-Apr-17	10
EM0001	UNPAID LEAVE	5-Aug-17	15-Aug-17	10
EM0002	UNPAID LEAVE	7-Mar-16	17-Mar-16	10
EM0002	UNPAID LEAVE	10-Oct-16	15-Oct-16	5
EM0002	ANNUAL LEAVE	1-Dec-16	31-Dec-16	30
EM0002	UNPAID LEAVE	11-Feb-17	16-Feb-17	5
EM0002	UNPAID LEAVE	9-Nov-17	14-Nov-17	5
EM0003	UNPAID LEAVE	10-Oct-16	15-Oct-16	5

Result

EMP CODE	NEXT ANNUEL LEAVE
EM0001	21-Apr-18
EM0002	11-Jan-18
EM0003

I have employee leave table, based on LEAVE TYPE = "ANNUAL LEAVE" and END DATE = MAX(END DATE) we have to calculate NEXT ANNUEL LEAVE
for example : EM0001 last annual leave on 31-MAR-17 after 365 days he got next annual leave but in between he take sum of unpaid leave 20 days,
so 20 days added in 365+20= 385 then his next leave date is 21-apr-18.

[Zubair_Muhammad]

@sohailstsindia

Try this MEASURE

Measure =
VAR Last_Date =
    CALCULATE (
        MAX ( TableName[END DATE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[EMP CODE] ),
            TableName[LEAVE TYPE] = "ANNUAL LEAVE"
        )
    )
VAR UnpaidLeaves =
    CALCULATE (
        SUM ( TableName[DAYS] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[EMP CODE] ),
            TableName[LEAVE TYPE] = "UNPAID LEAVE"
                && TableName[END DATE] > Last_Date
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( Last_Date ) ), Last_Date + UnpaidLeaves + 365 + 1 )

[Zubair_Muhammad · Accepted solution]

@sohailstsindia

[sohailstsindia]

Thanks you very much brother Zubair, its work perfectly.