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

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
 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 287 members 2,653 guests
Please welcome our newest community members: