Hello ,
I have an interesting case here.
I have dataset which has 3 months of data and data includes Emp ID, Date, Emp Designation, State, Town, City.
i would like to calculated the previous month # emp are there in it.
Note; if the emp got promoted then his designation will change also his location might change.
attached the data set here.
Thank you
Data set:
| Date | Emp ID | EMP Designation | State | Town | City |
| 01-12-2024 | A1234 | Associate 1 | IND | CA | T1 |
| 01-12-2024 | B2345 | Associate 1 | HKG | GK | T1 |
| 01-12-2024 | C3456 | Associate 1 | BPJ | JP | T1 |
| 01-12-2024 | D4567 | Associate 1 | DWR | WR | T1 |
| 01-12-2024 | E5678 | Associate 1 | SWR | WR | T1 |
| 01-12-2024 | F6789 | Associate 1 | UBL | BL | T1 |
| 01-12-2024 | G2583 | Associate 2 | SWE | WE | T2 |
| 01-12-2024 | H3691 | Associate 3 | UBL | BL | T2 |
| 01-01-2025 | A1234 | Associate 1 | IND | CA | T1 |
| 01-01-2025 | B2345 | Associate 1 | HKG | GK | T1 |
| 01-01-2025 | C3456 | Associate 1 | BPJ | JP | T1 |
| 01-01-2025 | D4567 | Associate 1 | DWR | WR | T1 |
| 01-01-2025 | E5678 | Associate 1 | SWR | WR | T1 |
| 01-01-2025 | F6789 | Associate 1 | UBL | BL | T1 |
| 01-01-2025 | G2583 | Associate 2 | SWE | WE | T2 |
| 01-02-2025 | A1234 | Associate 1 | IND | CA | T1 |
| 01-02-2025 | B2345 | Associate 1 | HKG | GK | T1 |
| 01-02-2025 | C3456 | Associate 1 | BPJ | JP | T1 |
| 01-02-2025 | D4567 | Associate 1 | DWR | WR | T1 |
| 01-02-2025 | E5678 | Associate 1 | SWR | WR | T1 |
| 01-02-2025 | F6789 | Associate 1 | UBL | BL | T1 |
| 01-02-2025 | G2583 | Associate 2 | SWE | WE | T2 |
| 01-02-2025 | H3691 | Sr. Associate | IND | CA | T3 |
| 01-02-2025 | K1476 | Sr. Associate | SWE | WE | T3 |
Result :
| Date | CM Month | PM Month |
| 01-02-2025 | 9 | 7 |
| 01-01-2025 | 7 | 8 |
| 01-12-2024 | 8 | 0 |
