DAX Average Headcount Calculator

/*
Calculates the Average Headcount for a company during a specified timeframe

Parameters
-----------
- Entity[Company]: Company to calculate the average headcount for
- Calendar[Date]: Date range within which to calculate the headcount

Returns
-------
- A decimal number representing the average headcount
*/

DEFINE
    VAR CurrentDate = MAX( Calendar[Date] )
    VAR AllDates = CALCULATETABLE(VALUES( Calendar[Date] ), ALL( Calendar ))
    
    VAR Headcounts =
        ADDCOLUMNS(AllDates,
            "ActiveEmployees", 
            CALCULATE(
                COUNTROWS(Payroll), 
                Payroll[HireDate] <= [Date], 
                ISBLANK(Payroll[TermDate]) || Payroll[TermDate] > [Date]))
                
RETURN
    AVERAGEX(
        Headcounts, 
        [ActiveEmployees])

This function performs the following steps:

1. Store the current date from the calendar table into a CurrentDate variable.
2. Retrieve all dates from the calendar table into an AllDates variable, ignoring any current filters.
3. For each date in AllDates, calculate the number of employees that were active on this day. An employee is considered active if their hire date is in the past, and they either have not terminated yet or their termination date is in the future. Store these counts in a Headcounts table.
4. Finally, calculate the average number of active employees across all dates in the Headcounts table.

This code can be used to analyze the average headcount in a time frame, which can help with workforce planning, budgeting and identifying recruitment trends over time. Always remember to learn best practices of DAX coding from Enterprise DNA platform and its insightful courses and learning materials.