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 count is returning basically no results for all dates in 2018. Where have i gone wrong? 

I want to be able to return '### staff were employed on 22/01/2018' or month on month active employee counts.

 

Count of Active Employees =
VAR currentdate =
    MAX( 'Table2'[Date])
RETURN
    CALCULATE(
        [Staff ID Count],
        FILTER (
            Table1,
            (Table1[StaffStartDate] <= currentDate
                && Table1[StaffTerminationDate] >= currentDate )
        ))

Need Help

4 Replies

  • DaFloDo's avatar
    DaFloDo
    Resolver I
    7 years ago

    Hi Anonymous,

     

    i think there coud be a current filter context on Table1. So you could try applying your time yondition to All(Talbe1).

     

    best regards

     

    florian

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Logic may need rework. What if someone's termination date was about a week ago and the currentdate is today. He will be counted as an active employee because you are filtering the table to allow any entries below the current date.

  • 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