Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)))
Solved! Go to 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
-- 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
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |