Count number of employees from start date to end date

[Ines_Raquel]

Hello, everyone.

I need to create a metric based on the following logic:

Nº Employees :=
calculate(DISTINCTCOUNT('Employee'[ID]),
filter('Employee table',
year('Employee table'[Entry Date]) <= 2021
&& ('Employee table'[Leave Date] = BLANK() || (year('Employee table'[Leave Date]) >=2021))))

However, when i filter the date on PowerBI, it only displays employees that have the entry date of the filtered year.

For instance, if i select 2021, i need to see all employees that have not left until 2021. Not solely those with the entry date at 2021 like it's currently happening.

Please, any help will be much appreciated!

Thanks.

[johnt75]

try

Nº Employees :=
CALCULATE (
    DISTINCTCOUNT ( 'Employee'[ID] ),
    REMOVEFILTERS ( 'Employee table' ),
    YEAR ( 'Employee table'[Entry Date] ) <= 2021
        && (
            ISBLANK ( 'Employee table'[Leave Date] )
                || ( YEAR ( 'Employee table'[Leave Date] ) >= 2021 )
        )
)

[Ines_Raquel]

I appreciate your help.

However, 'REMOVEFILTERS' is not being recognized on AS. 
2021 was more of an example. The year is supposed to be whatever is filtered.

Any work arounds?

[johnt75]

You could try

Nº Employees :=
VAR MaxDate =
    YEAR ( MAX ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Employee'[ID] ),
        ALL ( 'Employee table' ),
        YEAR ( 'Employee table'[Entry Date] ) <= MaxDate
            && (
                ISBLANK ( 'Employee table'[Leave Date] )
                    || ( YEAR ( 'Employee table'[Leave Date] ) >= MaxDate )
            )
    )

[Ines_Raquel]

I adptaded your formula a bit and got the right results for each year:

NumEmp:=
VAR endOfPeriod =MAX ( 'Dim Date'[Date])
VAR startOfPeriod = MIN( 'Dim Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT('Fact Internal Employee'[NIF Employee]),
FILTER (
ALL('Fact Internal Employee'),
( ('Fact Internal Employee'[Employment Date] <= endOfPeriod )
&& ('Fact Internal Employee'[Resignation Date] >= startOfPeriod || 'Fact Internal Employee'[Resignation Date] = BLANK()))
))
    )

The problem is that when i try to create an object with it against any other field, it presents repeated values:

Don't know if it helps, but the relationship is between Employee[entry date] and Date[Date]

[johnt75]

you're removing all the filters on the employee table. try

NumEmp :=
VAR endOfPeriod =
    MAX ( 'Dim Date'[Date] )
VAR startOfPeriod =
    MIN ( 'Dim Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Fact Internal Employee'[NIF Employee] ),
        REMOVEFILTERS ( 'Dim Date' ),
        'Fact Internal Employee'[Employment Date] <= endOfPeriod
            && (
                'Fact Internal Employee'[Resignation Date] >= startOfPeriod
                    || ISBLANK ( 'Fact Internal Employee'[Resignation Date] )
            )
    )

[Ines_Raquel]

Unfortunaly it doesn't work due to REMOVEFILTERS not being recognized. When i try to replace it with ALL i get an error saying "The expression contains multiple columns..." 😕

[johnt75 · Accepted solution]

Last thing I can think to try

NumEmp :=
VAR endOfPeriod =
    MAX ( 'Dim Date'[Date] )
VAR startOfPeriod =
    MIN ( 'Dim Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Fact Internal Employee'[NIF Employee] ),
        ALL ( 'Dim Date' ),
        'Fact Internal Employee'[Employment Date] <= endOfPeriod,
        (
                'Fact Internal Employee'[Resignation Date] >= startOfPeriod
                    || ISBLANK ( 'Fact Internal Employee'[Resignation Date] )
            )
    )

[Ines_Raquel]

Worker perfectly!
Thank you so much for your help!