cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sohailstsindia Regular Visitor
Regular Visitor

max date based on leaved days

EmployeeLeave
EMP CODELEAVE TYPEStart DATEEND DATEDAYS
EM0001ANNUAL LEAVE1-Jan-1631-Jan-1630
EM0001UNPAID LEAVE15-Mar-1620-Mar-165
EM0001UNPAID LEAVE10-Jun-1615-Jun-165
EM0001ANNUAL LEAVE10-Feb-1731-Mar-1749
EM0001UNPAID LEAVE10-Apr-1720-Apr-1710
EM0001UNPAID LEAVE5-Aug-1715-Aug-1710
EM0002UNPAID LEAVE7-Mar-1617-Mar-1610
EM0002UNPAID LEAVE10-Oct-1615-Oct-165
EM0002ANNUAL LEAVE1-Dec-1631-Dec-1630
EM0002UNPAID LEAVE11-Feb-1716-Feb-175
EM0002UNPAID LEAVE9-Nov-1714-Nov-175
EM0003UNPAID LEAVE10-Oct-1615-Oct-16

5

 

 

Result

EMP CODENEXT ANNUEL LEAVE
EM000121-Apr-18
EM000211-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
Super User

Re: max date based on leaved days

3 REPLIES 3
Super User
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
Super User

Re: max date based on leaved days

sohailstsindia Regular Visitor
Regular Visitor

Re: max date based on leaved days

Thanks you very much brother Zubair, its work perfectly.