cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tango1201 Frequent Visitor
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
tango1201 Frequent Visitor
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])

 Thanks for your help

View solution in original post

2 REPLIES 2
Community Support Team
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: | |
tango1201 Frequent Visitor
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])

 Thanks for your help

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 6 members 1,303 guests
Please welcome our newest community members: