Forum Discussion
Get active employee count based on start and end date range
- Anonymous9 years ago
So, say we are looking at April 2017. currentDate is going to be Apr 30, 2017 (max date in April).
FILTER (
ALL(Master),
Master[Start Date] <= currentDate && Master[End Date] >= currentDate
)Give me everybody that started on or before Apr 30 (that seems fine) AND ended on or after Apr 30 (not ok)?
So, if somebody ended Apr 15, they aren't included (because Apr 15 is not >= Apr 30).
How about:
Count of Active Employee =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Master ),
FILTER (
ALL(Master),
( Master[Start Date] <= endOfPeriod
&& Master[End Date] >= startOfPeriod)
)
) - 9 years ago
Worked perfectly. Thanks so much!
So, say we are looking at April 2017. currentDate is going to be Apr 30, 2017 (max date in April).
FILTER (
ALL(Master),
Master[Start Date] <= currentDate && Master[End Date] >= currentDate
)
Give me everybody that started on or before Apr 30 (that seems fine) AND ended on or after Apr 30 (not ok)?
So, if somebody ended Apr 15, they aren't included (because Apr 15 is not >= Apr 30).
How about:
Count of Active Employee =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )
VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Master ),
FILTER (
ALL(Master),
( Master[Start Date] <= endOfPeriod
&& Master[End Date] >= startOfPeriod)
)
)
- joshcomputer19 years ago
Helper V
Worked perfectly. Thanks so much!