cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Super User IV
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.

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
Super User IV
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.

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

SofG
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
Super User IV

Re: Calculating head cound using dax measure

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors