Forum Discussion
Submissions needed
Hi,
Could you share data in a simple Table format and show the desired result.
Its quite difficult to put into a simple table based on the complexities but essentially this is the data and desired result.
Employee Data: (shows who the employee is, when they were hired and exited (if left blank it means they are still employed) then it shows the Manager who that Employee reports to):
| Employee | Hiring Date | Exit Date | Manager |
| 1 | 1/1/19 | 14/6/19 | A |
| 2 | 21/4/19 | B | |
| 3 | 1/6/19 | B | |
| 4 | 14/1/20 | C |
Calendar Data:
The company has reporting weeks which do not align with a standard calender (and the reporting week changes every year so I cant just create a custom calender in BI which will remain consistent year on year). To resolve this the company has a manual calender template which they will populate each year which looks like this:
| Reporting week number | Week start date | Week end date |
| Week 1 | ||
| Week 2 |
(I understand this may be a bit confusing so please let me know if I need to explain in more detail)
The final desired result then needs to be:
| Manager | Number of submissions required |
| A | 2 |
| B | 3 |
| C | 1 |
To explain the process:
1) An employee is required to submit a report every week.
2) A manager is responsible for multiple employees
3) Based on the slicer selection of number of weeks, the dax needs to:
-Iterate through each employee and check when they were hired and exited
*If they were hired before the start of the sliced period then it starts counting the number of weeks from the beginning of the sliced period
*If they were hired after the start of the sliced period then it starts counting the number of weeks from the hired date
*If they exited before the end of the sliced period then it stops counting the number of weeks after they exited
*If they exited after the end of the sliced period or have not exited yet then it counts the number of weeks to the end of the sliced period
-Finally it takes those employees required submissions during the sliced period, looks to see which Manager that employee reports to and adds that to the Managers required submission (so if a Manager has 2 employees where employee 1 was required to submit 1 report and employee 2 was required to submit to reports the total required submissions for that Manager would be 3)
I understand this is quite complex so if you need me to explain something in more detail please indicate where.
If you look earlier in the thread I also attached a desktop file to look at.
Really appreciate the help!