Counting Current Staff - DAX not correct

[Anonymous]

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

[Anonymous]

Hi,

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

This might work in such a case:

[Anonymous]

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

[Anonymous]

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.

[DaFloDo]

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