cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prasa Regular Visitor
Regular Visitor

Display Employee Attendance

I have an excel file with two sheets. One sheet has the all the contract employees list by 'Name' and the other sheet called vacation calendar has 'Vacation Start', 'Vacation End', 'Type of Leave' (sick Leave, vacation) and 'EmployeeName'. 

How can I show the active employees list, sick leave list and vacation employees list as per that day/today?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display Employee Attendance

I'm going to assume you will be doing a refresh daily, after midnight.  I will also assume your Leave Table and Employee table are linked.

Create a calculated column in your leave table that is a simple True/False on whether the current line is presently active.  This could be a line of code like:

Leave Active = AND(
      TODAY() >= [Date Start],
      TODAY() <= [Date End]
)


Inside your Consultant Name table, we will have a calculated column for the amount of active leave

Active Leave Count = CALCULATE(
              COUNTROWS('LeaveTable'),
              'LeaveTable'[Leave Active]
)

 

When you create your Active Employee table, put on a filter that only shows employees who have an Active Leave Count of 0.

To get employees on Sick leave. Filter your leave table where active is True and type of leave is SL.

You should get the idea from now.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


5 REPLIES 5
Super User
Super User

Re: Display Employee Attendance

Hi,

 

Share a dataset and show the expected result.

prasa Regular Visitor
Regular Visitor

Re: Display Employee Attendance

Sheet1 has:

Consultant Name
Ross
Carita
Mike
Fiona
Barks
Ron
Kelley
Michael

Sheet2 : 

Date StartDate EndConsultant NameTypeofLeave
1/2/20165/2/2016RossSL
2/2/201610/2/2016CaritaVacation
3/2/20163/2/2016MikeSL
4/2/20168/2/2016FionaVacation
2/4/20182/15/2018BarksSL
2/23/20183/1/2018CaritaSL
3/2/20183/5/2018MikeVacation
2/11/20182/28/2018RossVacation
2/10/20183/10/2018MikeVacation

 

 

The expected result as per today 2/28/2017- It can be using any visual like multi row card or table..

 

Active Employees 

Fiona

Barks

Ron

Kelley

Michael

 

Sick Leave Employees 

Carita

 

Vacation Employees 

Ross

Mike

 

Thanks in advance for help.

Super User
Super User

Re: Display Employee Attendance

I'm going to assume you will be doing a refresh daily, after midnight.  I will also assume your Leave Table and Employee table are linked.

Create a calculated column in your leave table that is a simple True/False on whether the current line is presently active.  This could be a line of code like:

Leave Active = AND(
      TODAY() >= [Date Start],
      TODAY() <= [Date End]
)


Inside your Consultant Name table, we will have a calculated column for the amount of active leave

Active Leave Count = CALCULATE(
              COUNTROWS('LeaveTable'),
              'LeaveTable'[Leave Active]
)

 

When you create your Active Employee table, put on a filter that only shows employees who have an Active Leave Count of 0.

To get employees on Sick leave. Filter your leave table where active is True and type of leave is SL.

You should get the idea from now.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


prasa Regular Visitor
Regular Visitor

Re: Display Employee Attendance

Awesome, that solved it! Thank you Ross for the detailed explanation. 

Super User
Super User

Re: Display Employee Attendance

Hi,

 

Here's another solution.  I have used relative date filtering to always show you data for the present day.

 

Hope this helps.

 

Untitled.png