Forum Discussion
Count Employee allocations
I have allocation records, where an employee is allocated to various clients & the employee works at diff departments within the client, employees may be rotated or extended to another task within the client for some other department
Please refer below sample records
i.e. CHARANI is allocated to Godrej on 11 march to 31st Dec
i.e. Chaumel is allocated to LG on 15-March 21 (New Allocation), but again on 4-June-21 she is allocated to another department, since it is "Extension"
Sample Data:
| EmpID | Employee Name | Client | Department | Start Date | End Date | Status |
| 791 | CHAARANI | Godrej | Sales | 11-Mar-21 | 31-Dec-21 | New Allocation |
| 530 | CHAUMEL | LG | Marketing | 15-Mar-21 | 2-Dec-21 | New Allocation |
| 530 | CHAUMEL | LG | Production | 4-Jun-21 | 1-Apr-22 | Extension |
| 530 | CHAUMEL | Godrej | Account | 4-Apr-22 | 4-Dec-22 | New Allocation |
| 445 | VIGAN | Panasonic | Sales | 2-Feb-21 | 1-Mar-21 | New Allocation |
| 445 | VIGAN | Panasonic | Sales | 3-Mar-21 | 18-Mar-21 | Extension |
| 445 | VIGAN | Panasonic | Marketing | 22-Mar-21 | 1-Apr-21 | Extension |
| 445 | VIGAN | LG | Sales | 4-Apr-21 | 12-May-22 | New Allocation |
| 875 | GORTAIS | Panasonic | Production | 17-May-21 | 31-Dec-21 | New Allocation |
Problem Statement
Count "New Allocations" & "Extensions"
Notes:
1. If there is a single record in this sheet this means it is "New Allocation"
2. if there are multiple records of a same person, but the client name is same, the entry on the MIN(startdate) is considered "New Allocation" & rest are "Extensions"
3. if there are multiple records of a same person, every time employee is allocated to a new client, the entry on the MIN(startdate) is considered "New Allocation" & rest are "Extensions"
Output
| New allocations | 6 |
| Extensions | 3 |
Try to create a new column like below:
status_1 = var rank_ = RANKX( FILTER('Table', EARLIER('Table'[EmpID])='Table'[EmpID]&& 'Table'[Client]=EARLIER('Table'[Client]) ), 'Table'[Start Date],,ASC,Dense) return IF(rank_=1,"New Allocation ","Extension")
2 Replies
Try to create a new column like below:
status_1 = var rank_ = RANKX( FILTER('Table', EARLIER('Table'[EmpID])='Table'[EmpID]&& 'Table'[Client]=EARLIER('Table'[Client]) ), 'Table'[Start Date],,ASC,Dense) return IF(rank_=1,"New Allocation ","Extension")