Calculating open tasks

Hello

I have data like the following and would like to calculate how good the company is at closing assignments:
Blank ValidToDate means the assignment isnt closed yet.

+--------------+--------+----------------+----------------+--------------+----------+
| AssignmentId | CaseId | CaseCategory   | ValidFromDate  | ValidToDate  | CaseTeam |
+--------------+--------+----------------+----------------+--------------+----------+
| 1            | Case1  | AnswerCustomer | 01-01-2024     | 05-01-2024   | Team1    |
| 2            | Case1  | CloseCase      | 04-01-2024     | 09-01-2024   | Team1    |
| 3            | Case2  | AnswerCustomer | 08-01-2024     |              | Team1    |
| 4            | Case3  | MoveItems      | 08-01-2024     | 09-01-2024   | Team2    |
| 5            | Case4  | MoveItems      | 12-01-2024     |              | Team2    |
+--------------+--------+----------------+----------------+--------------+----------+


And a date table like the following

+------------+------+-------+------+
| Calendar   | Year | Month | Week |
+------------+------+-------+------+
| 01-01-2024 | 2024 | 1     | 1    |
| 02-01-2024 | 2024 | 1     | 1    |
| 03-01-2024 | 2024 | 1     | 1    |
| 04-01-2024 | 2024 | 1     | 1    |
| 05-01-2024 | 2024 | 1     | 1    |
| 06-01-2024 | 2024 | 1     | 1    |
| 07-01-2024 | 2024 | 1     | 1    |
| 08-01-2024 | 2024 | 1     | 2    |
| 09-01-2024 | 2024 | 1     | 2    |
| 10-01-2024 | 2024 | 1     | 2    |
| 11-01-2024 | 2024 | 1     | 2    |
| 12-01-2024 | 2024 | 1     | 2    |
| 13-01-2024 | 2024 | 1     | 2    |
| 14-01-2024 | 2024 | 1     | 2    |
| 15-01-2024 | 2024 | 1     | 2    |
+------------+------+-------+------+




The overview of what I want to achieve is the following:

+--------------+----------------+------------------+-----------------+
| Week of year | NewAssignments | ClosedAssignments | OpenAssignments |
+--------------+----------------+------------------+-----------------+
| 1            | 2              | 1                | 1               |
| 2            | 3              | 2                | 2               |
+--------------+----------------+------------------+-----------------+





However we report weekly. So in the report in want to show this weeks Target (which is last weeks OpenAssignments+NewAssignments this week)



So if say for instance todays date is 13-01-2024 (week of year=2) I would like the report to show:


Target this week Week2 (calculated as last weeks OpenAssignment+NewAssignments = 1+3)

4



ClosedAssignments this week
3



Help is greatly appreciated