Forum Discussion

mady90's avatar
mady90
Frequent Visitor
7 years ago
Solved

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, Ter...
  • Anonymous's avatar
    Anonymous
    7 years ago
    -- 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

Anonymous's avatar
Anonymous
Not applicable
7 years ago
-- 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's avatar
    SofG
    Frequent Visitor
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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