cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Garol Regular Visitor
Regular Visitor

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 )
        ))

4 REPLIES 4
DaFloDo Regular Visitor
Regular Visitor

Re: Counting Current Staff - DAX not correct

Hi @Garol,

 

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

Re: Counting Current Staff - DAX not correct

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.

Ahrne Frequent Visitor
Frequent Visitor

Re: Counting Current Staff - DAX not correct

Hi,

 

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

 

This might work in such a case:

CountStaff.PNG

Highlighted
Garol Regular Visitor
Regular Visitor

Re: Counting Current Staff - DAX not correct

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