Calculate Average Employee Headcount for a given day or month

[tango1201]

I am attempting to calculate a YTD average of how many employees we have for a given day or month.  For example and if we had 1,000 employees in Jan and 1,200 in Feb, the Feb YTD average would be 1,100.  I found the below DAX (modified from this blog) to be useful in calculating the number of employess on a given day but am now struggling with measuring a YTD average for a given day.  Any help is appreciated.

Thanks

Employee Count =
SUMX (
GENERATE (
CALCULATETABLE (
SUMMARIZE (
EmployeeRosterHires,
EmployeeRosterHires[Hire Date],
EmployeeRosterHires[Termination Date],
"Rows", COUNTROWS ( EmployeeRosterHires )
),
ALL ( 'Date' )
),
INTERSECT (
DATESBETWEEN ( 'Date'[Date], EmployeeRosterHires[Hire Date], EmployeeRosterHires[Termination Date] ),
LASTDATE ( 'Date'[Date] )
)
),
[Rows]
)

[Anonymous]

Hi @tango1201,

Have you try to use averagex to replace the sumx function?

Employee average Count =
AVERAGEX(
GENERATE (
CALCULATETABLE (
SUMMARIZE (
EmployeeRosterHires,
EmployeeRosterHires[Hire Date],
EmployeeRosterHires[Termination Date],
"Rows", COUNTROWS ( EmployeeRosterHires )
),
ALL ( 'Date' )
),
INTERSECT (
DATESBETWEEN ( 'Date'[Date], EmployeeRosterHires[Hire Date], EmployeeRosterHires[Termination Date] ),
LASTDATE ( 'Date'[Date] )
)
),
[Rows]
)

Regards,

Xiaoxin Sheng

[tango1201 · Accepted solution]

Substituting AVERAGEX for SUMX  in the Employee Count formula rendered that for every employee, there was one employee on average.  There's a meme there somewhere, for sure.

However and being that this was my first post, I accidentally posted it twice under two differents subject tags.  The response I received from the other post proved correct and is as follows:

AVG Daily = AVERAGEX(ALLSELECTED('Calendar'),[Employee Count])

 Thanks for your help