Forum Discussion

rajasekar_o's avatar
rajasekar_o
Helper V
2 years ago
Solved

login based leave calculation

hi team,
how to calculating leave 
I have employee logindetails 

EMPIDLOGIN DATETIME SHIFT
EMPS0011/4/20246.00 am1
EMPS0021/4/20246.00 am1
EMPJ0031/4/20246.00 am1
EMP0301/4/20246.00 am

1

EMP0321/4/20246.00 am

1

EMPS0012/4/20246.00 am

1

EMPS0022/4/20246.00 am1
EMP0302/4/20246.00 am1
    
    

 


EMPLOYEE

ID NAME
EMPS001KUMAR
EMPS002RAM
EMPJ003VENI
EMP030THAMO
EMP032VINOTH


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

Need Help
Tips and Tricks

8 Replies

  • rajasekar_o's avatar
    rajasekar_o
    Helper V
    2 years ago
    AllEmployeeDays =
    CROSSJOIN(
        'Emplv',
        'Calendar'
    )
    IT showing error

     

     

    • rajendraongole1's avatar
      rajendraongole1
      Super User
      2 years ago

      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's avatar
    rajendraongole1
    Super User
    2 years ago

    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.