HI,
I have below Source dataset
| Source data |
| Employee ID | Function | Start Date | End Date | Days |
| A001 | PM | 2024-01-01 | 2024-01-31 | 31 |
| A001 | PM | 2024-02-01 | 2024-02-15 | 15 |
| A001 | Consultant | 2024-02-16 | 2024-02-29 | 14 |
| A001 | PM | 2024-03-01 | 2024-03-31 | 31 |
| A001 | Consultant | 2024-04-01 | 2024-04-30 | 30 |
| | | | | 121 |
And i was hoping to produce the below summary
| Target summary |
| Employee ID | Function | Start Date | End Date | Days |
| A001 | PM | 2024-01-01 | 2024-02-15 | 46 |
| A001 | Consultant | 2024-02-16 | 2024-02-29 | 14 |
| A001 | PM | 2024-03-01 | 2024-03-31 | 31 |
| A001 | Consultant | 2024-04-01 | 2024-04-30 | 30 |
| | | | | 121 |
when using Group by function for Employee ID and Function with min for start date and max for end date. I got the below summary which is not correct.
| Group by Emplee ID and Function. - Incorrect Summary |
| Employee ID | Function | Start Date | End Date | Days |
| A001 | PM | 2024-01-01 | 2024-03-31 | 91 |
| A001 | Consultant | 2024-02-16 | 2024-04-30 | 75 |
| | | | Incorrect | 166 |
Does any expert have any good solution? Many thanks in advance.
