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

L

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.

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

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)

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…

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

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.

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.

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

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.