Measure for calculating colleague leavers vs. no. of employees

[robstewart89]

Hi,

I have a table of everyone in our company currently combined with the everyone who has left the company since Jan-2015. The two relevant columns i'm working with are joining date (for all existing and leavers) and a leaving date (only for leavers).

I basically need to find out the percentage of people who left the company by month. So leavers in Jan-2015 vs total employees in Jan-2015 and so on. I obviously have the leavers by month but i'm finding difficulty a) finding the total employees per month and b) being able to drill down by month, type of employee,region etc.

I was able to use the CALCULATE function to calculate how many employees were there per month but this was more of a manual measure by month:

2015-01 = CALCULATE(COUNTA(Sheet1[JoiningMonth]),Sheet1[JoiningMonth]<="2015-01")-CALCULATE(COUNTA(Sheet1[LeavingMonth]),Sheet1[LeavingMonth]<="2015-01",Sheet1[LeavingMonth]>"0")

Do i need to create some calculated columns/additional measures to make this work?

Any help/thoughts appreciated.

Thanks,

[Greg_Deckler]

@robstewart89 - Can you post some sample data?

[robstewart89]

@Greg_Deckler the table below shows an example of my data.

Person Ref	JoiningMonth	LeavingMonth
1001	          2005-02	
1002	          2008-01	  2015-01
1003	          2016-02	
1004	          2004-03	
1005	          2007-01	
1006	          2013-08	  2015-01
1007	          2015-11	  2016-03
1008	          2014-09	  2015-04
1009	          1997-06	  2015-05
1010	          1975-06	  2015-12

So in this example there would be:

2 leavers in "Jan-2015",

4 people who joined before Jan-2015 and are still here 

3 people who left after Jan-2015

1 person (1007) who joined and left after Jan-2015.

So this would be 2 leavers / 7 existing in Jan 2015 = turnover of 28.5%

And then replicate that for all months thereafter 

I bet i'm overlooking something really simple here but i've been round the houses with it!

Thanks,

Robert

[Anonymous]

Create another column:

eg. LeavingMonthMark = IF(LeavingMonth <> 0 , 1 , 0)

Then create a measure:

eg. YourPreferredMeasureName = DIVIDE(SUM(LeavingMonthMark) , COUNTROWS(TableName))

Put this in a visualization and it should work with any given month.

[robstewart89]

@Anonymous that's great, it gives me an overall % but as i have two differing date columns (joining and leaving) the only way i can see to get the % figure for February 2015 for example, is to filter joining month on February 2015 or before and the leaving month after Feb 2015. 

Unless there is a way to achieve this through a measure?

[Anonymous]

The best approach might be to build yourself a date table (as you're only concerned with months then a row per month would do instead) and then make use of these DAX Patterns.

The method we usually use to get the number of current employees would be to build a sum joining to date and a sum of leaving to date and then subtract the two (ammend the year to date pattern so that it's not limited to summing over the current year).

I suspect there's a more elegant way of doing it in a single DAX query, this is adapting from how we've done the same in MDX in our cubes.

[robstewart89]

I had looked into creating a date table (with just months as you said) and creating a relationship between that and my Master table. The problem I found was that as I have two date columns (joining and leaving) I couldn't link the date table to both columns - unless i'm missing some obvious workaround!

Thanks for the link on DAX Patterns Barney!

[Anonymous]

---

@robstewart89 wrote:

I had looked into creating a date table (with just months as you said) and creating a relationship between that and my Master table. The problem I found was that as I have two date columns (joining and leaving) I couldn't link the date table to both columns - unless i'm missing some obvious workaround!

Thanks for the link on DAX Patterns Barney!

---

A good point about the multiple dates. In the case of there being two date columns in Power BI you'll need two copies of your date table, one to join to each column.

I tend to forget about these things because I'm more used to Analysis Services Multidimensional where you can join the same table multiple times under different aliases. Sadly that doesn't exist yet for Power BI\Tabular Models to my knowledge.

[Nhallquist]

I think this is an example of a Slowly Changing Dimension.  Have you read through this blog post?

Slowly Changing Dimension

I