Hi There,
I'm currently working on employee daily log tracking. I have this database where each employee enters their daily task. As part of admin task, I want to see how many entries they missed/ forgot to do their entry.
Originally i just use simple total count of entries from the database less total count of workweeks (simple Mon-Fri)
So if I lay it out on a table visual with the Employee name, they will give the missing count. Problem is it's not including the employees start date or if the partner is resigned. So let's say, the work week (including holidays) is 262 days for 2020, New employees starting in between have an issue of many missing entries since the count still includes the dates before their start date.
EDIT: Sample data
| Employee Name | Start Date | End Date |
| Employee 1 | 9/1/2020 | |
| Employee 2 | 8/10/2020 | 10/15/2020 |
| Employee 3 | 1/1/2020 | |
For work week, I create a conditional column in my calendar table that for all Mon - Fri, set as 1 and 0 if not
Work Week = Count ('Calendar[workweek])
Log Count = Distinctcount('Database'[Log date])
Missing Entry = [Work Week] - [Log Count]
Sample Current Scenario for Year 2020
| Employee Name | Log Count | Work Week | Missing Entry |
| Employee 1 | 84 | 262 | 178 |
| Employee 2 | 31 | 262 | 231 |
| Employee 3 | 260 | 262 | 2 |
I want to incorporate in the count workweeks to just count the dates from where that employee started upto today. and same goes with if the partner resigned to stop the count of workweek after the resigned date
GOAL sample:
| Employee Name | Log Count | Work Week | Missing Entry |
| Employee 1 | 84 | 85 | 1 |
| Employee 2 | 31 | 45 | 14 |
| Employee 3 | 260 | 262 | 2 |
Sorry if it's a bit confusing. Let me know where can I further explain the scenario if it's not enough
