Timesheet reporting and linking on dates

[ChrisL567]

Good morning all, I'm confusing myself and could do with a little guidance please.

I'll start by saying I'm a complete novice on PowerBI and have muddled my way through things up until this point for the last few months. Let me try to explain what I am trying to achieve.

I want to create a timesheet report, which ultimately tell me if people haven't submitted a timesheet for a given day (for the moment I'm ignoring bank holidays).

So I have 4 tables which hold relevant information.

Calendar (which holds my date field)

People (Email address, PersonID)

TimesheetRecords (PersonID, Date, Hours Submitted) [this actually has a project code, but I'm not interested in that, I'm just returning a sum of hours submitted for the day)

So my first step works fine, I have my table build and it shows me date, email and total hours (example data below)..

Date	Email	Hours
30/11/2023	user1@email.com	7.5
30/11/2023	user2@email.com	6
30/11/2023	user3@email.com	2
30/11/2023	user4@email.com	0

I now need to link to my 4th table, as you can see, I only have a start & end date, along with hours per day.

Ultimately, what I want to report on is users where the sum of hours on timesheets and vacation hours on a given day is zero (excluding weekends - I'll worry about bank holidays another day).

I've linked my tables in the model, using Calendar.Date to Vacation.StartDate as the active relationship, and an inactive relationship of the Calendar.Date to Vacation.EndDate - but this breaks my results completely.

Could anyone help out a novice and point me in the right direction please?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...