Calculating head cound using dax measure

[mady90]

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

[Anonymous · Accepted solution]

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

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

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

[SofG]

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

[Anonymous]

Try this:

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

Best

D