Timesheet Report - Need to generate data to highlight missing timesheets for weeks no data submitted

Hi Ptown ,

According to your description, here are my steps you can follow as a solution.

(1)First, create a calculated column [Person_leave_date1] in the [employees] table.

Person_leave_date1 =
IF(ISBLANK('Employees'[Person_leave_date]),TODAY(),'Employees'[Person_leave_date])

(2)Then create a calculated table [Table].

Table =

var _employee=SUMMARIZE('Employees','Employees'[Person_Name])

var _date=SUMMARIZE('Timesheets','Timesheets'[Week Commencing])

var _crossjoin=CROSSJOIN(_employee,_date)

return

FILTER(_crossjoin,[Week Commencing]>=MAXX(FILTER('Employees','Employees'[Person_Name]=EARLIER([Person_Name])),'Employees'[Person_start_date])&&[Week Commencing]<=MAXX(FILTER('Employees','Employees'[Person_Name]=EARLIER([Person_Name])),'Employees'[Person_leave_date1]))

(3) Then create a calculated column [Flag] in the [Table] table.

Flag =

var _date=SUMMARIZE(FILTER('Timesheets','Timesheets'[Person_Name]=EARLIER('Table'[Person_Name])),Timesheets[Week Commencing])

return

IF([Week Commencing] in _date,0,1)

(4)Then create a calculated table [Timesheets1].

Timesheets1 =

var _append= SELECTCOLUMNS(FILTER('Table','Table'[Flag]=1),"Unique_Ref","Ref-missing","Person_Name",[Person_Name],"Week Commencing",[Week Commencing],"Activity","Missing Timesheet","Monday",7,"Tuesday",7,"Wednesday",7,"Thursday",7,"Friday",7,"Saturday",0,"Sunday",0,"cc_WeekHours",35)

return

UNION('Timesheets',_append)

(5) Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.