cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## max date based on leaved days

 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

3 REPLIES 3
Super User

## Re: max date based on leaved days

@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 )

```
Super User

Regular Visitor

## Re: max date based on leaved days

Thanks you very much brother Zubair, its work perfectly.