Track movement of employees - Attrition and movers

[Anonymous]

My raw data contains employee I'd department and directorate. Containing an extract each month with the current status of the employee. I.e. employee 12345 works in dept 1 as of 201912.

The employee would then repeat each month they continue to be employed, although may change departments or divisions.

I would like to track the movement of employee volumes, meeting the following the criteria
 

Leavers
New Starters
Movers

Between Divisions
Between Directorate
Between Cost Centre
Between Manager
From this i would like to then highlight the names / ID numbers moved within a selected period.
 
Power Bi File - Anon.pbix 
Excel raw files - Position Data Report - Anon.xlsx 
 
 I am unable to see how i can get this to work similar to  your previous solution  if you could help possible please?
 

[JarroVGIT · Accepted solution]

Hi @Anonymous ,

YOu are asking quite a bit here, so let's take it step by step. You combined your queries, which is good because you will need to have all the data from the different tabs in the excel sheet in one big table. First thing to do is to add a real date based on the Upload column. I added a calculated column like this:

DateStamp = DATE(LEFT(Combined[Upload], 4), RIGHT(Combined[Upload], 2), 1)

Now, before creating more measures and stuff, let's think about how we want to show the data. For your requirements, it seems that a table visual with months in the first column, number of leavers in the second column, number of starters in the third column etc would do the trick. That means that we are going to create measures for all your requirements (e.g. leavers, starters, and all type of movers). Let's start looking at leavers. The logic is, to sum all occurences of employeeID's that are in the previous month but not in the current month. The measure for this is:

Leavers = 
// We will count employees that are in the dataset of the previous month but not in current month. 
VAR _curDate = MAX(Combined[DateStamp])
VAR _prevMonthDate = PREVIOUSMONTH(Combined[DateStamp])
VAR _thisMonthTable = FILTER(ALL(Combined), Combined[DateStamp] = _curDate)
VAR _prevMonthTable = FILTER(ALL(Combined), Combined[DateStamp] = _prevMonthDate)
// We are adding a column to the _prevMonthTable, where we will count the occurences of every staff number in the _thisMonthTable. 
VAR _prevMonthLeavers = ADDCOLUMNS(_prevMonthTable, 
    "Left?", 
    VAR __curStaffNumber = [Staff Number]
    RETURN
    IF(COUNTROWS(FILTER(_thisMonthTable, [Staff Number] = __curStaffNumber)) > 0, 0, 1))
// Lastly; we are summing the newly created column in _prevMonthLeavers
RETURN SUMX(_prevMonthLeavers, [Left?])

On to the new starters. The logic is similar as leavers but reversed: we are counting staff numbers that are in the current month but not in the previous month. The measure for this is:

New Starters = 
VAR _curDate = MAX(Combined[DateStamp])
VAR _prevMonthDate = PREVIOUSMONTH(Combined[DateStamp])
VAR _thisMonthTable = FILTER(ALL(Combined), Combined[DateStamp] = _curDate)
VAR _prevMonthTable = FILTER(ALL(Combined), Combined[DateStamp] = _prevMonthDate)
VAR _thisMonthStarters = ADDCOLUMNS(_thisMonthTable, 
    "Started?", 
    VAR __curStaffNumber = [Staff Number]
    RETURN
    IF(COUNTROWS(FILTER(_prevMonthTable, [Staff Number] = __curStaffNumber)) > 0, 0, 1))
RETURN SUMX(_thisMonthStarters, [Started?])

Note that this is where I found that your dataset is a bit skewed; you are missing a lot of data for April 2019 and thus this makes it look like around 4000 people quit in April and 3800 started in May. Anyway, on to the Movers. I will focus on the Division movers, but the logic is the same for the others. It is again similar logic as above, but then with additional filters on the column Division:

Movers between Divisions = 
VAR _curDate = MAX(Combined[DateStamp])
VAR _prevMonthDate = PREVIOUSMONTH(Combined[DateStamp])
VAR _thisMonthTable = FILTER(ALL(Combined), Combined[DateStamp] = _curDate)
VAR _prevMonthTable = FILTER(ALL(Combined), Combined[DateStamp] = _prevMonthDate)
VAR _thisMonthMovedFromOtherDivision = ADDCOLUMNS(_thisMonthTable, 
    "Moved?", 
    VAR __curStaffNumber = [Staff Number]
    VAR __curDivision = [Division]
    RETURN
    IF(COUNTROWS(FILTER(_prevMonthTable, [Staff Number] = __curStaffNumber && [Division] <> __curDivision)) > 0, 1, 0))
RETURN SUMX(_thisMonthMovedFromOtherDivision, [Moved?])

Note that the IF statement is reversed (if TRUE then 1, otherwise 0, this was the other way around in the other measures).

This result in this table:

Now, then to your second question where you want to see who actually moved/joined/left. For this, we are going to need to create calculated columns to your dataset and filter on that while using a slicer. For example for leavers, I am going to create a calculated column that indicates if that person is leaving that month. (note; in the measure above I calculated everybody that left in March (last working day 31/03/2019) as a leaver in April. In this overview you will see who has its last month currently. So, selecting March will show the number of rows indicated by April in the table above!).

IsLastMonth = 
VAR _curStaffNumber = Combined[Staff Number]
VAR _curDate = Combined[DateStamp]
VAR _allMonthsCurStaff = FILTER(Combined, Combined[Staff Number] = _curStaffNumber)
RETURN
IF(MAXX(_allMonthsCurStaff, [DateStamp]) = _curDate, TRUE, FALSE)

Then you can create a slicer and tables like this:

Come to think of it, you can create simpler 'leavers' measures now we have this column, by summing calculated column IsLastMonth filtering on TRUE. Somthing like this:

CALCULATE(COUNTROWS(Combined), Combined[IsLastMonth] = TRUE())

Anyway, this might have been the most elaborated answer I have given on this community 😛 I think you have some stuff to think about and you can take it from here?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

Hi @JarroVGIT 

Thank you this is amazing, i had quickly read on my phone. Just about to boot up the laptop and give it a whirl

thanks again

👍👍👍👍👍👍👍

[JarroVGIT]

Good to hear! I had a bit more thought about it and I think the calculated column approach at the end of my post would be the cleanest. Create multiple TRUE/FALSE columns like "IsLastMonth" (to indicate the employee left) and "IsFirstMonth", "MovedDivision", "MovedCostCenter" etc. Then, create measures like I showed you in the last code bit. 

Altough the initial measures look cool, they are a bit overly complex to my taste 😛

Anyway, have fun and if you have trouble let me know. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

hi agian @JarroVGIT 

Thank you again. 

I have been through and cant get to work, so will have to revisit with a fresh mind in the morning. 

I have updated the data for April and also created a DateTable within the .pbix. However whereever i used the measures they returned the same number for all months/divsions. Then i also had problems with the calculated column, i will revisit again tomorrow and let you know how i get on.

I have used the files, so you can see my workings if you had the chance to review to identify my errors.

Thanks agian for the support so far 🙂

[JarroVGIT]

Hi I forgot to share my work file, that might help you?

https://1drv.ms/u/s!Ancq8HFZYL_aiIoSUZogxOpz7oa55A?e=yWKcUy

Otherwise, let me know tomorrow what is failing and I'll help you out 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[VaishnaviGandhi]

Hi @JarroVGIT 

I have a similar requirement and I tried your solution it does work, but what if I wanted to find out who those associates are who have been moved from divisions how would you go about finding that out ?as we have created a Table which has a movers column when i try to cross filter it doesn't shpow the value as per the domain, if reflect sum based on Months.

Looking forward to hearing from you

Thanks

[Anonymous]

Hi @JarroVGIT 

Thank you for this. I think im getting closer, however i think i have been naive and i didnt ask the right question, or only half of the question.

Ultimatly i am hoping to track where we are losing the staff from my directorate too (or gaining from), within the directorate to another division, leaving the company, or moving to another directorate / area of the business.

The top screenshot does show my original ask, however i think i didnt ask the right question and I have a feeling there maybe a way for me to structure the data better, if you have any suggestions. Or i may be over complicating what i want to achieve.

• Top left Blue = Total for each criteria each month (Movers, Leavers, Joiners)
• Top Right = current month totals
• Bottom Right Red = Was trying to identify how may leavers each month by directorate)
• Bottom Right Green = Movers in directorate

Then building on the next page you had created.

• Selecting a month = Yellow
• Top right purple = i would be able to see where losing people to
• Bottom right Orange = Staff moved (or could be leaving, starting) in their current directorate)
• Bottom Right = Where they have come through too.

Overall, I am hoping to track where we are losing the staff from my directorate too (or gaining from), within the directorate to another division, leaving the company, or moving to another directorate / area of the business.

Is this something that can be done with the data i have available, in its current format.

Current version with measures - Anon V2.pbix 

Original Anon.pbix 

Raw Excel file 

[jasontran]

Did you end up getting this to work? I'm looking to build something similar

My data is monthly employee snapshots appended as 1 table which I then use the calendar to return prior month to calculate and compare movements.

eg. Division (-1mth) =

VAR A = PREVIOUSMONTH('_calendar'[MonthEnd])

RETURN CALCULATE(

MAX(emp[Division]),

ALLEXCEPT(emp,emp[Employee ID]),

emp[Report Effective Date]=A)

Data presented is usually for the current reporting month and compared to prior month.

The trouble I have is if I present the last reporting month I have no idea how to show the movements out.

Rough output I can get to below (Oct 21 selected on calendar with respective division in a table visual)

Month End	Division	Headcount (-1 mth)	Hires	Move In	Move Out	Exits	Headcount
Oct-21	Admin	25	0	5	?	0	26
Oct-21	Finance	40	2	2	?	3	40
Oct-21	Marketing	33	5	0	?	3	35
Oct-21	Legal	10	0	1	?	0	10
Oct-21	IT	50	2	5	?	6	47