Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mady90
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
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors