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

[Ptown]

Hi, thanks in advance to anyone who can help me with the following.

I have created a report that shows data from a SharePoint weekly timesheet system. My report is fully working apart from one shortfall: If a person does not submit a timesheet for a given week the timesheet system does not generate an entry to say there is a missing timesheet.

I'm trying to get the PBI report to create an entry to flag the missing timesheet but I've been struggling with the challenge thus far.

I have a table of employees with the dates they started working for the company. And with the end dates for any people that have left the company. Everyone should submit at least one entry every week. (Even if it's just an entry to say they were on vacation).

And I have a table of entries by week. One person can submit multiple rows for the same week (i.e. one for each activity they do). But if they submit nothing (for a week when they are employed by the company) I need the report to generate a row for them. This row should have activity equal to "Missing Timesheet" and have a set number of hours assigned to it (for simplicity it could just be 7 hours per day, ideally it would be 7 hours on weekdays and 0 on weekends).

I have created a dummy pbix file with dummy data. You will notice that

• Carl has not submitted anything for week commencing 10th October. Therefore I need to generate the "Missing Timesheet" entry for Carl for this week.
• Bryony leaves the company on 23rd Oct. So the fact she doesn't submit a timesheet for week commencing 24th Oct is expected. Nothing needs to be generated.
• Dora joins the company on 24th Oct. Nothing needs to be generated for her for the previous weeks.

Here are some screeshots from that dummy file:

Thanks again for any help or guidance.

[BayanKhalaf]

Hello @Ptown 
how did you create the report? am trying to create the same report but i can't find a way to get the data or export it from sharepoint

[Anonymous · Accepted solution]

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. 

[Ptown]

Thank you, that is fantastic and does exactly what I need. The concept makes perfect sense & the act of transferring this to my real model will give me the opportunity to fully understand each component.

FYI In your step 4 I changed "Ref-missing" to the following:

"RefM-" & CONCATENATE(FORMAT([Week Commencing],"yyyy-MM-dd-"),[Person_Name])

So that my Unique_Ref column will continue to have unique entries no matter how many missing timesheets there may be.

Thanks again!