Login DATE Based leave calculation

[rajasekar_o]

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

[bhanu_gautam]

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

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

[Anonymous · Accepted solution]

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.