Forum Discussion
% Staff Turnover Measure
Hi there,
I am still fairly new to Power BI. Whilst I am now more familar with how to calculate Monthly Staff Turnover and YTD Staff Turnover. I am not sure how to work out the number of employees at the beginning of the month and end of the month to be able to input these values into the staff turnover formula.
I have two tables:
1. Current Staff Data
2. Leaver Staff Data
Both tables are the exact same structure (leaver data is simply a cut and paste of the employee entry in the current staff data) - one represents leavers and one represents currently hired staff. The only difference is the Leavers Table contains an Actual Post End Date column.
The data looks like this (with all identifying information removed):
1. Current Staff Data
| Employee No | Domain | Team | Current Post End Date | |
| 11004439 | SAS | Strategy, Architecture and Support/Strategy and Architecture/Business Analysis | ||
| 2153456 | PASM | Product and Service Mgmt/Service Operations/Deskside Support | ||
| 9402751 | ENG | Engineering/Infrastructure/Datacentres | ||
| 10095216 | PASM | Product and Service Mgmt/Service Operations/Deskside Support | 01/03/2022 | |
| 11182695 | PASM | Product and Service Mgmt/Service Operations/Support Centre | 16/07/2022 | |
| 10283718 | ENG | Engineering/Platforms and Innovation/CRM | 29/07/2022 | |
| 10990597 | PASM | Product and Service Mgmt/Service Operations/Support Centre | 31/08/2022 | |
| 10227025 | ENG | Engineering/Infrastructure/Datacentres | ||
| 10798077 | RIT | Research IT/Research Lifecycle Programme |
2. Leaver Staff Data
| Employee No | Domain | Team | Current Post End Date | Actual Post End Date |
| 10318971 | ODIT | IT Supplier Management | 01/01/2022 | 01/01/2022 |
| 10765145 | ODIT | Project Management/PMO | 03/01/2022 | 03/01/2022 |
| 5189438 | TIO | Networks and Security/Networks | 16/01/2022 | 16/01/2022 |
| 10615129 | ODIT | Marketing and Comms | 26/01/2022 | 26/01/2022 |
| 9248303 | SIAM | Service Delivery/Service Delivery Management | 03/02/2022 | 03/02/2022 |
| 7473469 | ODIT | Project Management/PMO | 16/03/2022 | 16/03/2022 |
| 9840817 | SDA | Testing | 31/03/2022 | 31/03/2022 |
| 10864015 | SIAM | Service Operations/Deskside Support | 31/03/2022 | 31/03/2022 |
| 10525288 | SDA | Testing | 05/04/2022 | 05/04/2022 |
Any advice would be greatly appreciated! Thank you.
Hi Anonymous ,
There seems to be something wrong with the data you gave.
If you want to count the number of employees from the beginning to the end of the month, you can try the following formula.
last_mount = VAR selectedDate = LASTDATE ( 'Date'[Date] ) VAR selectedDateMonthsBefore = NEXTDAY ( DATEADD ( selectedDate, -1, MONTH ) ) RETURN SUMX ( 'Employees', IF ( [End Date] >= selectedDateMonthsBefore && [End Date] <= selectedDate, 1, 0 ) )
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
There seems to be something wrong with the data you gave.
If you want to count the number of employees from the beginning to the end of the month, you can try the following formula.
last_mount = VAR selectedDate = LASTDATE ( 'Date'[Date] ) VAR selectedDateMonthsBefore = NEXTDAY ( DATEADD ( selectedDate, -1, MONTH ) ) RETURN SUMX ( 'Employees', IF ( [End Date] >= selectedDateMonthsBefore && [End Date] <= selectedDate, 1, 0 ) )
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.