topic DAX measure for staff turnover in DAX Commands and Tips

DAX measure for staff turnover

LearnPowerBI5 — Mon, 26 Sep 2022 14:07:03 GMT

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.

Re: DAX measure for staff turnover

amitchandak — Tue, 27 Sep 2022 02:26:46 GMT

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-trend/ba-p/882970

Re: DAX measure for staff turnover

Anonymous — Tue, 27 Sep 2022 08:20:13 GMT

Hi LearnPowerBI5 ,

Have trouble to follow.

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

Best Regards,

Jay

Re: DAX measure for staff turnover

LearnPowerBI5 — Wed, 28 Sep 2022 14:24:33 GMT

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