Starters and leavers from monthly extract

[Anonymous]

Hi,

I currently record the changes in headcount for a large team, with data coming from a monthly extract. Currently I count this simply by counting no. of unique IDs per month.

I now want to be able to find out what is causing the the changes from month to month. E.g. if I can see that between Jan and Feb count goes from 560 to 550, what makes that up - is it 10 leavers, or 15 new starters and 25 leavers for example?

I know in theory this should be easy, as the data shows when a unique ID first starts/ ends. However so far, I have only been able to create a matrix which kind of shows what I am looking for, looks awful though.

Example of data set used:

Employee ID  ,  Extract month  ,  Boring employee info stuff

1312313           Jan                      XXXXXXXXXXX 
1231231           Jan                      XXXXXXXXXXX
2534556           Jan                      XXXXXXXXXXX
4356546           Jan                      XXXXXXXXXXX

2534556           Feb                      XXXXXXXXXXX
4356546           Feb                     XXXXXXXXXXX

[v-cherch-msft]

Hi @Anonymous

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Regards,

Cherie

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may refer to below measures. Attached the simplified sample file.

New Starters =
VAR lastmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
    )
RETURN
    IF (
        MAX ( Table1[MonthNo] ) = 1,
        CALCULATE ( COUNT ( Table1[Employee ID] ) ),
        CALCULATE (
            COUNT ( Table1[Employee ID] ),
            FILTER ( Table1, NOT ( Table1[Employee ID] ) IN lastmonth )
        )
    )

Leavers =
VAR lastmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
    )
RETURN
    IF (
        MAX ( Table1[MonthNo] ) = 1,
        0,
        CALCULATE (
            COUNT ( Table1[Employee ID] ),
            FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 )
        )
            - CALCULATE (
                COUNT ( Table1[Employee ID] ),
                FILTER ( Table1, Table1[Employee ID] IN lastmonth )
            )
    )

Regards,

Cherie