How to create a payroll report with unique employee names

I am working on a payroll system wherein a report is generated for a pay period with a unique employee list. I have the following tables:
Employees - Email (Key), Employee Name, Timesheet Log (REF_ROWS (“Timesheet”, “Name”)), …
Timesheet - Log ID (Key), Name (Ref Employee), Pay Period ID(Ref), …
Pay Period - ID (Key), Start Date, End Date, Related Timesheets, …

How do I create a report for the Timesheet entries of employees created withing the pay periods with a unique list of employees?

The report would be something like this,
Employee Name: XYZ
Total Hours: 127
Total Salary: 312
Total Absent: 0

Employee Name: XYZ
Total Hours: 127
Total Salary: 312
Total Absent: 0
etc…
This is my expression that I have added to a Virtual column in [Pay Period], to get a list of entries within the period. How to also get the unique employees list?

SELECT(Timesheet[Log ID],
AND
([Date/Time In]>=[_THISROW].[Start Date],
[Date/Time In]<=[_THISROW].[End Date]

))

Would really appreciate any help in this. Thanks.

You’ll probably need to use nested START expressions, like Timesheets within Employees. And use FILTER() to generate the List of key values.

2 Likes

Thanks for putting me on the correct path. I am still learning to use expressions, but I have managed to get a list of Unique Employee Names that match the Timesheet entries using the below start expression.

<<Start: Select(Employees[Email], IN([Email],Select(Timesheet[Name], AND([Date/Time In] >= [_THISROW].[Start Date], [Date/Time In] <= [_THISROW].[End Date]), TRUE)))>><<[Employee Name]>>

I’m failing to understand how I can calculate the sum only for each listed employee name.
This is what I get,

Thanks.

1 Like

You need to add another condition to your SUM(SELECT()) to match the employee in each START iteration.

2 Likes

Thanks Marc, I’ve got it!
Here is the final expression for the Total Hours (Overtime) for each employee within the respective pay period.

<<SUM(Select(Timesheet[Number of Hours (Overtime)],
AND
([Date/Time In]>=[_THISROW].[Start Date],
[Date/Time In]<=[_THISROW].[End Date],
[Name]=[_THISROW-1].[Email])))>>
3 Likes