I would like hard data (i.e. not the output of a formula). I would prefer that the solution leverage PowerQuery and create a new table (Table 😧 MissingTimesheetsTbl. However if my PWR BI Family ❤ thinks otherwise; no worries, I’ll go with a DAX solution.
I envision a table as follows:
StaffMemberName column: a list of staff names (derived from the staff table) in column A.
Week Start Dates: Week start dates in column B (dates start on Monday, and would be repeated for each staff member)
TimesheetSubmittedYN column: a look up against the Timesheet table, which checks if for Staff member (e.g. Jo Name) a row exists for the corresponding week start date. If a timesheet exist, out "Yes", if no timesheet exist out "No", if the date is in the future i.e. date greater than now() + 7 days out put "Pending"
TimesheetID Column: the ID number of the timesheet from the timesheet table, if none exist output “Missing Sheet”; if the date is in the future i.e. date greater than now() + 7 days output "Pending".
I would like to create Table 😧 MissingTimesheetsTbl
This is a good solution @HotChilli thank you. Could I possibly leave this problem open in favour of a PowerQuery solution. I would really like the cross joined table to be created at run time as physical table.