login based leave calculation

[rajasekar_o]

hi team,
how to calculating leave 
I have employee logindetails 

EMPID	LOGIN DATE	TIME	SHIFT
EMPS001	1/4/2024	6.00 am	1
EMPS002	1/4/2024	6.00 am	1
EMPJ003	1/4/2024	6.00 am	1
EMP030	1/4/2024	6.00 am	1
EMP032	1/4/2024	6.00 am	1
EMPS001	2/4/2024	6.00 am	1
EMPS002	2/4/2024	6.00 am	1
EMP030	2/4/2024	6.00 am	1

EMPLOYEE

ID	NAME
EMPS001	KUMAR
EMPS002	RAM
EMPJ003	VENI
EMP030	THAMO
EMP032	VINOTH

IF the employee don't have login date than consider as leave 
(all days are working day)

[rajasekar_o]

@rajendraongole1 
WHERE ican use 

this measure 

AllEmployeeDays =

CROSSJOIN(

    'Emplv',

    'Calendar'

)

[rajasekar_o]

AllEmployeeDays =

CROSSJOIN(

    'Emplv',

    'Calendar'

)
IT showing error

[rajendraongole1]

Hi @rajasekar_o -you have to create a calculated table using cross join funcion with Calendar and Employee tables to get a complete list of all employee dates that should have a login. 

AllEmployeeDays =

CROSSJOIN(

    'Emplv',

    'Calendar'

)

Please change or replace with your model level table names.

[rajendraongole1]

Hi @rajasekar_o - I have loaded both tables along with created a new date table calendar as like below and given a relationship between all three as like below

used calculated tables to create a calendar table as below:

Calendar =

CALENDAR(

    MIN('Leave'[LOGIN DATE]),

    MAX('Leave'[LOGIN DATE])

)

Create a cross-join between the Calendar and Employee tables to get a complete list of all employee dates that should have a login.

AllEmployeeDays =

CROSSJOIN(

    'Emplv',

    'Calendar'

)

Create a calculated column the leaves by checking which dates do not have corresponding login entries in above new as leaves

Leave =

IF(

    COUNTROWS(

        FILTER(

            'Leave',

            'Leave'[EMPID] = 'AllEmployeeDays'[ID ] &&

            'Leave'[LOGIN DATE] = 'AllEmployeeDays'[Date]

        )

    ) = 0,

    "Leave",

    "Present"

)

Hope this helps.

[rajasekar_o]

@rajendraongole1 I tried this, but it showed an error. Could you please share the application file?

[rajendraongole1]

Hi @rajasekar_o - Can you please upload the file in drive, i will look and update you on the same.

[rajasekar_o]

@rajendraongole1 
https://drive.google.com/file/d/1RIKJBPOVGVOHOQrRgDttBg15Q_qK3io5/view?usp=sharing

[rajendraongole1 · Accepted solution]

Hi @rajasekar_o - Please find the attached file. 

Hope this helps