Forum Discussion
Employee Count - Returns strange numbers
Hello all, I have an emloyee table and created a measure called employee count. Employee Count = VAR selectedDate = MAX('Date'[Date]) RETURN CALCULATE(COUNTROWS('All Employees'), FI...
Thank you for the idea! It gives diferent numbers but they stil lare not correct and it finds employees who were terminated before they were hired. Using a different measure I can calculate it properly:
Current Employees = CALCULATE(COUNTx(FILTER('All Employees','All Employees'[DTHired]<=max('Date'[Date]) && (ISBLANK('All Employees'[DTTerminated]) || 'All Employees'[DTTerminated]>max('Date'[Date]))),('All Employees'[Employee Id])),CROSSFILTER('All Employees'[DTHired],'Date'[Date],None))
Greg_Deckler
Community Champion
Community ChampionAnonymous Maybe try this:
Employee Count =
VAR __selectedDate = MAX('Date'[Date])
VAR __Table =
FILTER(
ALL('All Employees'),
[DTHired] <= __selectedDate && ( [DTTerminated] = BLANK() || [DTTerminated] >= __selectedDate )
)
VAR __Result = COUNTROWS(DISTINCT(__Table))
RETURN
__ResultThank you for the idea! It gives diferent numbers but they stil lare not correct and it finds employees who were terminated before they were hired. Using a different measure I can calculate it properly:
Current Employees = CALCULATE(COUNTx(FILTER('All Employees','All Employees'[DTHired]<=max('Date'[Date]) && (ISBLANK('All Employees'[DTTerminated]) || 'All Employees'[DTTerminated]>max('Date'[Date]))),('All Employees'[Employee Id])),CROSSFILTER('All Employees'[DTHired],'Date'[Date],None))