topic Re: Employee Turnover Based on Month in DAX Commands and Tips

Employee Turnover Based on Month

Andonip — Fri, 17 May 2024 13:59:40 GMT

Hello,

New user here and trying to build out our Employee Turnover figure. Usually in excel we have a view per month, the takes the date of the month and employee ID, so we can monitor month-on-month changes of various demographics.

Right now my data set looks something like this:

Period	Employee ID	First Name	Last Name	Department
1	123	John	Smith	Finance
2	444	Jane	Doe	Sales
3	668	Bruce	Wayne	Customer Support
1	123	John	Smith	Tax
2	444	Jane	Doe	Sales
3	668	Bruce	Wayne	Customer Support

What i find though is that my formula does no recognise the employee ID per period:

headcount = CALCULATE(DISTINCTCOUNT('Attrition HC'[Employee ID],FILTER('Attrition HC','Attrition HC'[Period]=1)))

So as a result, I am just counting everyone once rather than per month. Is this normal behaviour or is there something i can do to fix this?

Best regards,

Re: Employee Turnover Based on Month

xifeng_L — Fri, 17 May 2024 15:28:43 GMT

Hi Andonip ,

Is the following result what you want?

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Employee Turnover Based on Month

Andonip — Fri, 17 May 2024 17:16:52 GMT

Hi Xifeng,

Note exactly I suppose what I was trying to do with the calculation was if we for example take a year to date view, the calulcation would take me leavers, then divide by the distinct count of each period. It would besically allow me to see a true headcount each month.

Kind regards,

Re: Employee Turnover Based on Month

Anonymous — Tue, 21 May 2024 03:10:02 GMT

Thanks for the reply from xifeng_L,, please allow me to provide another insight:
Hi,Andonip

For your first requirement, my understanding is that you need to group by each different period and calculate the number of people who always have a few different employee ids in each period:

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

headcount = CALCULATE(DISTINCTCOUNT('Attrition HC'[Employee ID]),FILTER(ALLSELECTED('Attrition HC'),'Attrition HC'[Period]=MAX('Attrition HC'[Period])))

The Allselected() function retains, the filtering conditions that you grouped with the Max() function, thus satisfying your need to count once per period, rather than once per person.
Below is the content of the relevant documentation:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

3.Here's my final result, which I hope meets your requirements.

4.Secondly regarding your desire to count all the people and then divide by a different number of people in each period, my measure is as follows:

Measure = DIVIDE(CALCULATE(DISTINCTCOUNT('Attrition HC'[Employee ID]),FILTER(ALLSELECTED('Attrition HC'),'Attrition HC'[DATE]>=DATE(2024,1,1)&&'Attrition HC'[DATE]<=TODAY())), CALCULATE(DISTINCTCOUNT('Attrition HC'[Employee ID]),FILTER(ALLSELECTED('Attrition HC'),'Attrition HC'[Period]=MAX('Attrition HC'[Period]))))

5.My end result is as follows:

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.