How to work out Attrition for Staff

[rush]

I have a table with Staff data with columns DateEmployed & TerminationDate and a DateTable.

I would like to work out the number of people that started & left (which I used a count formula) as well as the net growth for all date periods.

The formula would count each DateEmployed as 1 & if an individual does not have a Termination Date then it would not count it. e.g. 4 people starts in June 2016 & 2 leaves in June 2016 giving me a net value of 2.

I would like to display all 3 dimensions of data in one graph as well.

My formula does not count correctly & brings up incorrect data.

Starting: Measure counting the people starting in each month or year.

Starting =
CALCULATE (
    COUNT ( 'Table'[FullName] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                NOT ISBLANK ( [DateEmployed] )
                    && MONTH ( EARLIER ( 'Table'[DateEmployed] ) ) = MONTH ( MAX ( DateTable[Date] ) )
            )
        )
    )
)

DateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

Starting: Measure counting the people starting in each month or year.

Starting =
CALCULATE (
    COUNT ( 'Table'[FullName] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                NOT ISBLANK ( [DateEmployed] )
                    && MONTH ( EARLIER ( 'Table'[DateEmployed] ) ) = MONTH ( MAX ( DateTable[Date] ) )
            )
        )
    )
)

Ending: Measure for counting the people ending in each month or year.

Ending =
CALCULATE (
    COUNT ( 'Table'[FullName] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                NOT ISBLANK ( [TerminationDate] )
                    && MONTH ( EARLIER ( 'Table'[TerminationDate] ) )
                        = MONTH ( MAX ( DateTable[Date] ) )
            )
        )
    )
)

Net: Total for each month:

Total = ABS([Starting] - [Ending]) 

[v-sihou-msft · Accepted solution]

@rush

In this scenario, since you need display the [starting] and [ending] in same graph, the easiest way is creating two calculated tables, one for Staff Employed, the the ohter for Staff Terminated. Each table can build relationship to Calendar table with date column. Then you just need to create simple measures like "=COUNTA('Table'[FullName])", it will be aggregated on corresponding level when putting the Calendar hierarchy into a chart visual.

Regards,

[v-sihou-msft · Accepted solution]

@rush

In this scenario, since you need display the [starting] and [ending] in same graph, the easiest way is creating two calculated tables, one for Staff Employed, the the ohter for Staff Terminated. Each table can build relationship to Calendar table with date column. Then you just need to create simple measures like "=COUNTA('Table'[FullName])", it will be aggregated on corresponding level when putting the Calendar hierarchy into a chart visual.

Regards,

[toirshad]

Hi,

I am new to Power BI. Could you please explain the same with sample data / tables. 

Regards,

[rush · Accepted solution]

Hi Thank you very much. It works well.