topic Re: Count number of employees from start date to end date in DAX Commands and Tips

Count number of employees from start date to end date

Ines_Raquel — Tue, 08 Nov 2022 12:39:56 GMT

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.

Re: Count number of employees from start date to end date

johnt75 — Tue, 08 Nov 2022 14:44:48 GMT

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

Re: Count number of employees from start date to end date

Ines_Raquel — Tue, 08 Nov 2022 15:11:50 GMT

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?

Re: Count number of employees from start date to end date

johnt75 — Tue, 08 Nov 2022 15:16:15 GMT

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

Re: Count number of employees from start date to end date

Ines_Raquel — Tue, 08 Nov 2022 15:52:05 GMT

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]

Re: Count number of employees from start date to end date

johnt75 — Tue, 08 Nov 2022 16:58:09 GMT

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

Re: Count number of employees from start date to end date

Ines_Raquel — Tue, 08 Nov 2022 17:28:50 GMT

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..." 😕

Re: Count number of employees from start date to end date

johnt75 — Wed, 09 Nov 2022 09:50:32 GMT

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

Re: Count number of employees from start date to end date

Ines_Raquel — Thu, 10 Nov 2022 10:46:06 GMT

Worker perfectly!
Thank you so much for your help!