Splitting data over months when using week commencing to define capacity

[Joynul]

Hi, 

I am currently working on creating a capacity report, but I am having an issue where by my data pulls in the number of days a user has worked during week commencing X, but when the week commencing towards the end of the month and a new month starts mid week, my report double count, to avoid this I am trying to look for a way to say if a user works 5 days on a project on the week commencing 28/08/23. then the calculation would be able to say that he worked on the project on the 

28/08/23 - 01/09/23, which ,means I could get the report to show the user worked 4 days in August and 1 day in September, 

whereas another user who worked 4 days on the same project on the 28/08/2023 then it would only show the first four days i.e  28/08/23 - 31/08/23

Just to add the data only shows the number of days an employee works on a project within a week, it doesn't break the down to the exact day that the employee worked, hence why I am looking into this workaround

This is an example of the current data set that I am working with

Max_days.Name	Week_Comm	Wrkd_Days	Index	ind_wrk_week	Days_Worked	Remaining_Capacity
Employee Name 1	28-Aug-23	2.0	1	5.0	0.0	0.0
Employee Name 1	28-Aug-23	3.0	2	5.0	0.0	0.0
Employee Name 2	28-Aug-23	3.0	3	4.0	0.0	1.0
Employee Name 2	28-Aug-23	1.0	4	4.0	0.0	1.0
Employee Name 1	06-Jun-22	1	5	1	0	4
Employee Name 2	25-Jul-22	4	6	4	0	1
Employee Name 3	01-Aug-22	4	7	0	4	0
Employee Name 4	08-Aug-22	4	8	4	0	1

[lbendlin]

- Months and Weeks are not compatible

- Use your Calendar table to spell out exactly what you mean by Month, Week, Workday, Holiday etc. Good luck if your users are in different countries/time zones.