Forum Discussion
Solved
Average Active EEs for Date Range
Hi, I have a tableof employees with Hire, Termination and Rehire Dates. I also have a date table Im trying to get the average amount of active employees for whatever date range is selected on th...
I created the measure below and it works for now! Thanks so much for all your help!!
Hi MStark ,
Please try to update the formula of measure [Active Employee] as below and check if it can return the correct result...
Active Employee =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'EE Info'[Full Name] ),
FILTER (
'EE Info',
(
VALUE (
YEAR ( 'EE Info'[Hire Date] )
& IF (
MONTH ( 'EE Info'[Hire Date] ) < 10,
"0" & MONTH ( 'EE Info'[Hire Date] ),
MONTH ( 'EE Info'[Hire Date] )
)
)
<= VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
&& (
ISBLANK ( 'EE Info'[Termination Date] )
|| VALUE (
YEAR ( 'EE Info'[Termination Date] )
& IF (
MONTH ( 'EE Info'[Termination Date] ) < 10,
"0" & MONTH ( 'EE Info'[Termination Date] ),
MONTH ( 'EE Info'[Termination Date] )
)
)
> VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
)
)
|| (
VALUE (
YEAR ( 'EE Info'[Rehire Date] )
& IF (
MONTH ( 'EE Info'[Rehire Date] ) < 10,
"0" & MONTH ( 'EE Info'[Rehire Date] ),
MONTH ( 'EE Info'[Rehire Date] )
)
)
<= VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
&& 'EE Info'[Employee Status] = "Active"
)
)
)
Best Regards
Still doesnt seem to work... does a relationship between the calendar and EE info table need to be active? I played around with measure Greg_Deckler provided and its working for now
Thanks so much for your time and assistance!