Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PWR BI Family ❤,
I would like to create a report for managers, so that they can check which staff have missing timesheets.
I would like to know for every [WeekStart] in my WeekStartDatesTbl; if a staff member has submitted their timesheet or not.
I have three tables to start with and I would like to create a fourth.
Find the data and tables here: PWRBI_missingtimesheets.pbix
Starting tables:
Table A: WeekStartDatesTbl
Table B: StaffNamesTbl
Table C: TimesheetsTbl
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".
Solved! Go to Solution.
I had a little play around with this.
I created a new table
AllWeeksAllStaff = CROSSJOIN(WeekStartDatesTbl, StaffNamesTbl)That gives you all the possible staff timesheets. You then have to link up with the existing
DateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]and also a similar one in the existing timesheets table
DateName = FORMAT(TimesheetsTbl[WeekCommencing], "YYYYMMDD") & TimesheetsTbl[StaffMemberName]You then create a relationship between them, on this field.
I had a little play around with this.
I created a new table
AllWeeksAllStaff = CROSSJOIN(WeekStartDatesTbl, StaffNamesTbl)That gives you all the possible staff timesheets. You then have to link up with the existing
DateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]and also a similar one in the existing timesheets table
DateName = FORMAT(TimesheetsTbl[WeekCommencing], "YYYYMMDD") & TimesheetsTbl[StaffMemberName]You then create a relationship between them, on this field.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |