Get active employee count based on start and end date range

[joshcomputer1]

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 of the month don't seem to be counted. I want those to be included. Any employee that is active during the month. How do I change the formula so that instead of current date, it looks for the first day of this month and last day of this month? (see code below). The table name is "Master" and columns are "start date" and "end date". There is an "active?" column, but I don't think this is needed in this instance.   

http://community.powerbi.com/t5/Desktop/Calculating-a-monthly-employee-count-from-a-start-and-end-da...

Count of Active Employee =
VAR currentDate =
     MAX ( 'DateTable1'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Master ),
        FILTER (
            ALL(Master),
               ( Master[Start Date] <= currentDate
                         && Master[End Date] >= currentDate )

[Anonymous · Accepted solution]

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)
        )
    )

[Anonymous · Accepted solution]

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 · Accepted solution]

Worked perfectly. Thanks so much!