Need to calculate the Average headcount over 12 months

[frosty911]

Hi Community,

I am working with some employee data. I need to create a measure to calculate the running 12 month average from static data. (Sample data below):

Employee ID	Department	Start End	End Date	Current Status
10000	HR	1/1/2023		Active
10001	IT	1/1/2023		Active
10002	IT	1/1/2023	12/1/2023	Terminated
10003	HR	1/1/2023		Active
10004	HR	1/1/2023		Active
10005	Sales	2/1/2023		Active
10006	HR	2/1/2023		Active
10007	Finance	2/1/2023	5/1/2023	Terminated
10008	Finance	2/1/2023	7/1/2023	Terminated
10009	Finance	2/1/2023		Active
10010	Finance	3/1/2023		Active
10011	IT	5/1/2023	7/1/2023	Terminated
10012	Sales	5/1/2023		Active
10013	Sales	5/1/2023		Active
10014	Sales	5/1/2023	11/1/2023	Terminated
10015	Sales	7/1/2023	11/1/2023	Terminated
10016	Sales	7/1/2023		Active
10017	Admin	8/1/2023		Active
10018	Finance	8/1/2023	11/1/2023	Terminated
10019	Finance	8/1/2023		Active
10020	IT	8/1/2023		Active
10021	Sales	9/1/2023	10/2/2023	Terminated
10022	Sales	9/1/2023		Active
10023	Sales	9/1/2023		Active
10024	Sales	10/1/2023		Active
10025	HR	10/1/2023		Active
10026	Finance	10/1/2023	12/1/2023	Terminated
10027	Finance	10/1/2023		Active
10028	Finance	11/1/2023		Active
10029	Finance	11/1/2023		Active
10030	IT	12/1/2023		Active

I have a department slicer, so the final average should account for the slicer changes as well. 

I am facing the following challenges:

1. Accounting for the Hires and leavers differences to get the MoM headcount over 12 months

2. Every month, this list will keep growing with more hires and leavers, so need something that can work as a rolling average.

Help on this would be much appreciated. thanks so much in advance!

[frosty911]

Edit: Column name is Start Date not Start End. 

Thanks

[CoreyP]

Yeah, that's going to require some efforts! It took us quite a bit of work to get it right. 

Basically, your first objective is to get a count of active employees by date. However, your employee data
only contains start date and termination date. You can't use either of these to trend current headcount,

because you'll just be aggregating headcount by the date they were hired or terminated. What we did was transform into a table that had fields [Employee ID] and [AsOfDate]. Each employee would have a record for every date they were active between their hire date and termination date. And for active employees, you need to replace the terminaton date with today's date. 

So, as an example, employee A was hired 12/1/2023, and employee B was hired 12/6/2023, and terminated 12/10/2023, the table would look like:

EmployeeID	AsOfDate
A	12/1/2023
A	12/2/2023
A	12/3/2023
A	12/4/2023
A	12/5/2023
A	12/6/2023
B	12/6/2023
A	12/7/2023
B	12/7/2023
A	12/8/2023
B	12/8/2023
A	12/9/2023
B	12/9/2023
A	12/10/2023
B	12/10/2023
A	12/11/2023

[frosty911]

Thanks for the quick reply. is there any quick formula to transform the data into this fomat?

[CoreyP]

You can use this formula if you wanted to do it in DAX as opposed to PQ:

fact Employee = GENERATE( 'dim Employee' , CALENDAR( 'dim Employee'[Start Date] , IF( ISBLANK( 'dim Employee'[Termination Date] ) , TODAY() , 'dim Employee'[Termination Date] ) ) )

This was my sample data:

And this is the output:

[CoreyP]

Then you can count your active:

[CoreyP]

From there, you can create a measure for active employees by using a distinct count. Once you have that, then you can have an averagex for rolling period averages.

[frosty911]

Hi @CoreyP 

Thanks for the help so far. 

I have managed to get the rolling average following your steps.

However, when i plot the Rolling Average on a line chart, for some reason, i get the future month as well, and the values dont match the rolling average value 

I am expecting it to stop at November and show the value 25.25

Here is my rolling average formula

Active Employees rolling average final =

IF(

    ISFILTERED('Calendar'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = ENDOFMONTH('Calendar'[Date].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'Calendar'[Date].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('Calendar'),

                    'Calendar'[Date].[Year],

                    'Calendar'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE([Active Employees], ALLSELECTED('Calendar'[Date].[Day]))

        )

)