Logic to recognize an employees Start & End Date given a report that identify missing time entries

[wyleereilly]

Hi Power BI Community,

I've been struggling for a few days here to figure this one out. Hoping someone here can help me diagnose. 

Background

• My Professional services team is responsible for logging 8 hours a day in our time management software (which I have connected to Power BI)
• The objective of the report is to essentially identify the 'delta' between the employee's time logged and the 8 hours they're responsible for logging--i.e., did the log their 8 hours on that given day, and what is the difference
• As for databases:
• I have a 'Time Entries' Data Table that has historical time logged by employee
• & a 'Workweek' Data Table that is essentially a Date Table, with a 'Workweek' column that has 8 hours, unless a holiday is in the system for that day, in which case it becomes a 0.

Below is a screenshot example - so given a date, I display the 'Time - Hours' (Time Logged by Employee) from the Time Entries table, the amount of time they are responsible for logging (factoring in holidays), and a 'Workday Delta', which is calculated as follows:

Note: 'Difference between workday and holiday' = 'Workweek' column shown below:

Database Structure

Below is the schema, with the relationship between Time Entries and Workweek as follows:

• Employees & Time entries table is simply connected by User ID

My Employees Table holds the start/end date:

And I've also merged queries into my Time Entries Table to see if a related relationship would impact this at all.

The Challenge

I can't figure how to add a measure/column that recognizes the End & Start Date of each employee, and 0's our the Workday Delta accordingly

• At this time, when using my slicer on date, technically all of my employees, regardless of whether they've left or not, are responsible for logging their 8 hours
• Additionally, when going back in time, all of my employees who haven't started yet shouldn't show up in the filtered visualizations

[lbendlin · Accepted solution]

For such scenarios you may want to consider using a disconnected calendar table.  That will allow you to compute the measure also for dates where no user has recorded their data (even if they should have).

[wyleereilly]

Thank you for responding- I did end up using a master date table, but due to the custom nature of some of the calculations I also made a new underlying dataset as well to be more compatible.