Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rush
Helper V
Helper V

How to work out Attrition for Staff

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]) 

 

2 ACCEPTED SOLUTIONS
v-sihou-msft
Employee
Employee

@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,

View solution in original post

Hi Thank you very much. It works well.

 

 

View solution in original post

3 REPLIES 3
v-sihou-msft
Employee
Employee

@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,

Hi,

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

Regards,

Hi Thank you very much. It works well.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.