Forum Discussion

rajasekar_o's avatar
rajasekar_o
Icon for Helper V rankHelper V
2 years ago
Solved

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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

rajasekar_o , 

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 - PresentEmployees



Create 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 details

  • rajasekar_o's avatar
    rajasekar_o
    Icon for Helper V rankHelper V
    2 years ago

    bhanu_gautam 
    I try not working.
    can you please share the application

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.