DAX measure for staff turnover

[LearnPowerBI5]

Hi,

I want to create a measure (DAX) that calculates the staff turnover rate for me the formula I am using is no. of employees resigned divided by the average no. of employees in a period (actual column names - no. of resources resigned/ (no. of emp at start of the month + no. of emp at end of month/2)). 

NOTE: the no. of emp at the start and end of the month is Jan and whichever month is currently going on (it must be dynamic and work as system date) 

Currently, in the pbix file I have figured out how to calculate the emp count at the start of the month, however, I want a complete formula not like creating a separate formula for each component and I'm unable to figure it out. 

Request you to help me with this. Below is the link to the file for reference.

https://t.ly/SL74 

Thanks in advance.

[v-jayw-msft]

Hi @LearnPowerBI5 ,

Have trouble to follow.

Would you please share the expected result to help us better understand the logic?

Best Regards,

Jay

[LearnPowerBI5]

Hi,

I have attached the image for reference. And below is the method through which I have done the calculation. 

How I calculated staff turnover?

1. Sum the total no. of resources joined

2. Sum the total no. of resources resigned

3. Sum the no. of resources at the start of the month (i.e., a sum only values for the month of January since it is the start of the year)

4. Sum the no. of resources at the end of the month (sum only the values for the month of September this must be dynamic like system date or today’s date and if want to check the year).

So I require help to create a measure for the above. 

Thanks

[amitchandak]

@LearnPowerBI5 , USually the number of employee End of Period/Month and start of period/Month are

Current Employees EOP = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Current Employees SOP = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=Min('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>Min('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...