Forum Discussion

joshcomputer1's avatar
9 years ago
Solved

Get active employee count based on start and end date range

  Hi there,   This is a continuation of a post from a while back.  You can find the link below.  A problem that I am running in to with this formula is that employees who are active for only part ...
  • Anonymous's avatar
    Anonymous
    9 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)
            )
        )

     

  • joshcomputer1's avatar
    joshcomputer1
    9 years ago

    Worked perfectly. Thanks so much!