Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Counting Current Staff - DAX not correct

Hi there   I'm trying to return the count of staff that are employed on any given day/month/year. Table1 contains the staffID, StartDate and TerminatedDAte. Table2 is a date dimension.  The current...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

 

How does the TerminatedDate look for staff still working? Do you account for blanks?

 

This might work in such a case:

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks Ahrne, i think you're onto something. I have not handled the blanks in the terminated date column. 

When taking on your query, i'm getting the following error. 
 
 
The syntax for ';' is incorrect.
(DAX
(
VAR currentdate = MAX('Date'[Date])
VAR staffCount =
CALCULATE(
COUNTROWS(Dim_Staff);Dim_Staff[StaffTerminationDate]>currentdate) +
CALCULATE(
COUNTBLANK(Dim_Staff[StaffTerminationDate]);Dim_Staff[StaffStartDate] < currentdate)
RETURN staffCount