Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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
3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

Thanks you very much brother Zubair, its work perfectly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.