Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 )
Thanks you very much brother Zubair, its work perfectly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |