% Staff Turnover Measure

[Anonymous]

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.

[Anonymous]

Thank you! This worked. 

[v-henryk-mstf · Accepted solution]

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
        )
    )

related link

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.