Forum Discussion
Changing team assignment solution
Hello everyone, I have the following problem when customizing my report. We already have a report that tracks the workload of our project teams. Simplified, the report consists of a project table (p...
Hi Schwabinsky
I have a general idea of what to do but without a sample data it's hard to test a solution. Please provide a workable sample data (not an image), your expected result from that and the reasoning behind.
Hi denextian, thank you for your fast response. In the following, ich will show you the example data.
Iputdata:
dim_Employee:
| EmployeeID | EmployeeName | Team | StartDate | EndDate |
| 1001 | Max Mustermann | Team A | 01.01.2024 | 01.01.2100 |
| 1001 | Max Mustermann | Team B | 01.01.2000 | 31.12.2023 |
| 1002 | Jane Dow | Team C | 01.01.2024 | 01.01.2100 |
| 1002 | Jane Dow | Team D | 01.01.2000 | 31.12.2023 |
dim_Date:
| Date | DateKey | Year | YearMonth | Quarter | DayOfMonth | CalendarWeek | DayOfYear |
| 30.12.2023 | 20231230 | 2023 | 2023-12 | 4 | 30 | 52 | 364 |
| 31.12.2023 | 20231231 | 2023 | 2023-12 | 4 | 31 | 52 | 365 |
| 01.01.2024 | 20240101 | 2024 | 2024-01 | 1 | 1 | 1 | 1 |
| 02.01.2024 | 20240102 | 2024 | 2024-01 | 1 | 2 | 1 | 2 |
| 03.01.2024 | 20240103 | 2024 | 2024-01 | 1 | 3 | 1 | 3 |
| 04.01.2024 | 20240104 | 2024 | 2024-01 | 1 | 4 | 1 | 4 |
| 05.01.2024 | 20240105 | 2024 | 2024-01 | 1 | 5 | 1 | 5 |
fact_Projecthours:
| Project | Projectprocess | Date | FK_Employee | Hours |
| 101898 | 500 | 01.01.2024 | 1001 | 4 |
| 101898 | 510 | 02.01.2024 | 1001 | 4 |
| 101898 | 520 | 03.01.2024 | 1002 | 4 |
| 101898 | 530 | 04.01.2024 | 1002 | 4 |
| 101900 | 600 | 01.01.2024 | 1001 | 6 |
| 101900 | 610 | 02.01.2024 | 1001 | 6 |
| 101900 | 620 | 03.01.2024 | 1002 | 6 |
| 101900 | 630 | 04.01.2024 | 1002 | 6 |
The following visual shows the data as it should look if a date between “01.01.2024” and “01.01.2100” is selected via a date slicer. If you now change the date to a date in the past in the period from “01.01.2000” to “21.12.2023”, then the team of Max Mustermann should change to “Team B” and the team of “Jane Dow” should change to “Team D”:
| Drillthrough (Team > Employee > Project) | January | February | March | April | May | June | July | August | September | October | November | December |
| Team A | 16 | |||||||||||
| Max Mustermann | 16 | |||||||||||
| 101898 | 16 | |||||||||||
| Team C | 24 | |||||||||||
| Jane Dow | 24 | |||||||||||
| 101900 | 24 |
Thank you!
Schwabinsky