cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating head cound using dax measure

Hi Everyone,

I have 2 tables

1 table is date table with following fields: date, month, quarter, year, yearmonth,yearnumber

2nd table is Employee table with following fields: EmpID, Hired Date, Termination Date,
employees that are active have a blank termination date.

I am trying to do employee head count by month and year. Wrote a dax measure using forums but it is showing me blank results:

Would be gratefull for any help here.

```HeadCount =
var endperiod=MAX('date'[Date])
var startperiod=MIN('date'[Date])
return
CALCULATE(DISTINCTCOUNT('shared User'[EmployeeId]),FILTER(ALL('shared User'),('shared User'[HireDate]<=endperiod && 'shared User'[TerminationDate]>=startperiod)))```
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Calculating head cound using dax measure

```-- Assumption is that an Employee is
-- active within a period of time [__start, __end] if
-- the intersection with [HireDate, TerminationDate]
-- is not empty, that is
-- intersection(
--		[__start, __end],
--		[HireDate, TerminationDate]
--	) is not empty.
-- This is logically equivalent to saying
-- that it's NOT TRUE that
-- HireDate > __end OR TerminationDate < __start,
-- which means
-- HireDate <= __end AND TerminationDate >= __start.
-- An adjustment is needed when TerminationDate is BLANK.

VAR __end = MAX ( 'date'[Date] )
VAR __start = MIN ( 'date'[Date] )
CALCULATE (
DISTINCTCOUNT ( 'shared User'[EmployeeId] ),
'shared User'[HireDate] <= __end,
OR(
'shared User'[TerminationDate] >= __start,
ISBLANK( 'shared User'[TerminationDate] )
)    )
RETURN

This will work on the assumption that the 'date' table is not connected to the 'shared User' table. If it is, then a different formula would be required. And this also depends on which relationships are active between the tables.

Best

Darek

3 REPLIES 3
Super User IV

## Re: Calculating head cound using dax measure

```-- Assumption is that an Employee is
-- active within a period of time [__start, __end] if
-- the intersection with [HireDate, TerminationDate]
-- is not empty, that is
-- intersection(
--		[__start, __end],
--		[HireDate, TerminationDate]
--	) is not empty.
-- This is logically equivalent to saying
-- that it's NOT TRUE that
-- HireDate > __end OR TerminationDate < __start,
-- which means
-- HireDate <= __end AND TerminationDate >= __start.
-- An adjustment is needed when TerminationDate is BLANK.

VAR __end = MAX ( 'date'[Date] )
VAR __start = MIN ( 'date'[Date] )
CALCULATE (
DISTINCTCOUNT ( 'shared User'[EmployeeId] ),
'shared User'[HireDate] <= __end,
OR(
'shared User'[TerminationDate] >= __start,
ISBLANK( 'shared User'[TerminationDate] )
)    )
RETURN

This will work on the assumption that the 'date' table is not connected to the 'shared User' table. If it is, then a different formula would be required. And this also depends on which relationships are active between the tables.

Best

Darek

Frequent Visitor

## Re: Calculating head cound using dax measure

Hi,

I am trying to use this formula as a measure. But it says that "A function 'calculate' has been used in a true/false expression that is used as a table filter expression. This is not allowed". Do you know what I did wrong?

Thanks!

Kind regards

Super User IV

## Re: Calculating head cound using dax measure

Try this:

```HeadCount =
VAR __end = MAX ( 'date'[Date] )
VAR __start = MIN ( 'date'[Date] )
CALCULATE (
DISTINCTCOUNT ( 'shared User'[EmployeeId] ),
'shared User'[HireDate] <= __end,
OR(
'shared User'[TerminationDate] >= __start,
'shared User'[TerminationDate] = BLANK()
)
)
RETURN

Best

D

Announcements