cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate Average Employee Headcount for a given day or month

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Calculate Average Employee Headcount for a given day or month

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

2 REPLIES 2
Community Support Team

## Re: Calculate Average Employee Headcount for a given day or month

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

## Re: Calculate Average Employee Headcount for a given day or month

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 1,303 guests
Recent signins: