Employee Headcount

DAX DAX Calculations
1

Help please:

I have been using the below formula for headcount - this appears to work correctly for top line number of colleagues - however when it is filtered down to team level it doesn’t give the correct headcount.

I essentially need the exact number of employees who are with us on any given date.

My headers on my data include:
Date Joined
Date Left
Department
Leaving code Description

No of Colleagues = CALCULATE([Joining],FILTER(ALL(Dates[Date]),Dates[Date]<=AVERAGE(Dates[Date])))-CALCULATE([Leaving],FILTER(ALL(Dates[Date]),Dates[Date]<=AVERAGE(Dates[Date])),ALL('CORE DATA'[Leaving Code Description]))

Can anyone help please?

L

2

Sure happy to look into this but need to know more.

There’s a lot at play here so need to understand the data within the tables, how you setup your data model including the relationships you have and also what is the current context of the calculation.

Unfortunately with DAX it’s never a simple as just reviewing the formula by itself, there always more to it.

If you want to add a demo model here with the scenario then that will speed up assistance.

Chrs

Just as a side note on this as well as what you’re looking for is very very similar to a technique called ‘Events in Progress’

Check out this tutorial at Enterprise DNA Online for how it works. I think you might go aha when you watch this and how it is set up as it’s quite unique

Also another real-world example of it can be found here using a slight different formula, but calculates exactly the same then I believe you are after.

Starting around the 19min mark

See how you go with these and let me know how you go.

3

Hi Sam
Thanks for your response - Sales and Orders in Progress was one of the tutorials I watched and thought it would give me what I wanted - however I couldn’t get it to work for me.

I have attached the relationships

Relationships
Relationships.PNG784×545 21.2 KB

Other formulas that are contained in the Number of colleague formula are below:

**Joining** = VAR Joining = 
CALCULATE(
    COUNT('CORE DATA'[Date Joined]),
    USERELATIONSHIP(Dates[Date],'CORE DATA'[Date Joined]),
    ALL('CORE DATA'[Leaving Code Description])) 
    return 
    if(Joining=BLANK(),
        0,
        Joining
        )

**Leaving** = if(COUNT('CORE DATA'[Date Left])=BLANK(),0,COUNT('CORE DATA'[Date Left]))

data.xlsx (8.5 KB)

4

Ok so for what you were originally looking for these a couple of things that stand out.

Not sure on these current formula you have.

It can be much simpler.

The main problem I see as to why the events in progress technique is not work is because you need both your relationships from the date table to be ‘inactive’.

Currently you have one active and one inactive.

You see here in the tutorial…

image
image.jpg830×416 44.9 KB

Then you can just use the same technique used in the video also.

image
image.jpg788×142 31.8 KB

You would just need to sub in your measure - which would be something like COUNTROWS( CoreData )

Then also the correct dates in the right place.

I’ve had a look at the data and I’m pretty confident this is exactly what you need and if setup correctly will work.

image
image.png755×221 6.81 KB

It’s almost exactly the same technique.

I also just ran a workshop yesterday which again cover this exact problem and how to deal with it.

Check out around the 22 min mark and for a few minutes after that.

5

Thanks Sam

I think I may be almost there - I think the only thing I am missing is I want to count colleagues if they are still here as well i.e the leave date is blank.

How can I add this in to the formula?

Loren

6

Ok not too difficult here.

There’s a couple of ways you could do it.

Probably the easiest and simpliest way is to create another column in your data table.

You can create a simple calculated column that normalises the Date Left.

With something like this.

Normalised Date Left = 
IF( ISBLANK( DateLeftColumn ), TODAY() + 1, DateLeftColumn )

What this would do is give you a date in every row. You would then use this column to link back to your date table and remove the existing inactive relationship with the Date Left column.

Then re-arrange your formula to iterate over this new column you’ve created with the normalized dates.

This is honestly the quickest way to do this.

You could also complete it in DAX formula but I think this will simplify it for other calc you may want to complete as well.

See how you do with this one.