Forum Discussion
Login DATE Based leave calculation
hi,team
Login date based leave calculation heare all day are working day
Employee
| EMPID | NAME | DEPT |
| EMPS001 | KUMAR | accounts |
| EMPS002 | RAM | IT |
| EMPJ003 | VANI | TESTING |
| EMP030 | THAMO | accounts |
| EMP032 | VINOTH | IT |
login
| EMPID | LOGIN DATE | TIME | SHIFT |
| EMPS001 | 01-04-2024 | 6:00 AM | 1 |
| EMPS002 | 01-04-2024 | 6:00 AM | 1 |
| EMPJ003 | 01-04-2024 | 6:00 AM | 1 |
| EMP030 | 01-04-2024 | 6:00 AM | 1 |
| EMPS001 | 02-04-2024 | 6:00 AM | 1 |
| EMPS002 | 02-04-2024 | 6:00 AM | 1 |
| EMP030 | 02-04-2024 | 6:00 AM | 1 |
expected output
if i filter the date 1/4/2024
Department wise leave
if i filter the date 2/4/2024
Department wise leave
employee wise leave details with
if i filter the date 1/4/2024
if i filter the date 2/4/2024
Thanks for the reply from bhanu_gautam , please allow me to provide another insight:
Hi, rajasekar_o
Regarding the issue you raised, my solution is as follows:
1. Below are the measure I've created for your needs:
Leave count = VAR ID1 = CALCULATETABLE ( VALUES ( 'login'[EMPID] ), ALLSELECTED ( 'login' ) ) VAR cc1 = CALCULATE ( COUNT ( Employee[DEPT] ), FILTER ( 'Employee', NOT ( 'Employee'[EMPID] IN ID1 ) ) ) RETURN IF ( ISFILTERED ( 'login'[LOGIN DATE] ), IF ( ISBLANK ( cc1 ), 0, cc1 ), 0 )Login = VAR ID2 = CALCULATETABLE ( VALUES ( 'login'[EMPID] ), ALLSELECTED ( 'login' ) ) RETURN IF ( ISFILTERED ( 'login'[LOGIN DATE] ), IF ( NOT ( MAX ( 'Employee'[EMPID] ) IN ID2 ), "Leave", BLANK () ), BLANK () )2.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Ensure that the LOGIN DATE column is in date format.
Create a relationship between the EMPID in the Employee table and the EMPID in the Login table.Create a measure to count the number of logins per day.
Total Logins = COUNT(Login[EMPID])
Create a measure to count the number of employees per department.Employees Present = DISTINCTCOUNT(Login[EMPID])Employees Absent =VAR TotalEmployees = COUNTROWS(Employee)VAR PresentEmployees = [Employees Present]RETURN TotalEmployees - PresentEmployeesCreate a calculated column to determine if an employee was present on a specific date.
Is Present =IF(COUNTROWS(FILTER(Login,Login[EMPID] = Employee[EMPID] &&Login[LOGIN DATE] = SELECTEDVALUE(Login[LOGIN DATE]))) > 0,"Yes","No")Use a slicer to filter the data by LOGIN DATE.
Create visuals to show department-wise and employee-wise leave detailsbhanu_gautam
I try not working.
can you please share the applicationThanks for the reply from bhanu_gautam , please allow me to provide another insight:
Hi, rajasekar_o
Regarding the issue you raised, my solution is as follows:
1. Below are the measure I've created for your needs:
Leave count = VAR ID1 = CALCULATETABLE ( VALUES ( 'login'[EMPID] ), ALLSELECTED ( 'login' ) ) VAR cc1 = CALCULATE ( COUNT ( Employee[DEPT] ), FILTER ( 'Employee', NOT ( 'Employee'[EMPID] IN ID1 ) ) ) RETURN IF ( ISFILTERED ( 'login'[LOGIN DATE] ), IF ( ISBLANK ( cc1 ), 0, cc1 ), 0 )Login = VAR ID2 = CALCULATETABLE ( VALUES ( 'login'[EMPID] ), ALLSELECTED ( 'login' ) ) RETURN IF ( ISFILTERED ( 'login'[LOGIN DATE] ), IF ( NOT ( MAX ( 'Employee'[EMPID] ) IN ID2 ), "Leave", BLANK () ), BLANK () )2.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
