Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
)
Solved! Go to 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
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
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |