Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
Hi,
Share a dataset and show the expected result.
Sheet1 has:
Consultant Name |
Ross |
Carita |
Mike |
Fiona |
Barks |
Ron |
Kelley |
Michael |
Sheet2 :
Date Start | Date End | Consultant Name | TypeofLeave |
1/2/2016 | 5/2/2016 | Ross | SL |
2/2/2016 | 10/2/2016 | Carita | Vacation |
3/2/2016 | 3/2/2016 | Mike | SL |
4/2/2016 | 8/2/2016 | Fiona | Vacation |
2/4/2018 | 2/15/2018 | Barks | SL |
2/23/2018 | 3/1/2018 | Carita | SL |
3/2/2018 | 3/5/2018 | Mike | Vacation |
2/11/2018 | 2/28/2018 | Ross | Vacation |
2/10/2018 | 3/10/2018 | Mike | Vacation |
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.
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |