Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prasa
Helper I
Helper I

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
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.