How to calculate Headcount over time

[sam.mckay]

Hi Frank,

I’ve seen this question a few times and I believe I have the solution for you.

It’s similar to this forum post here.

Staff Population

Hi, New to DAX and trying to do a staff hire and leaving graph. I want to accumulate the staff as they join the company and then want to offset the people who are leaving. Attached a data set and image of what I am trying to look at. The main point that I am trying to show is that a lot of people would be leaving in a month and does the company have plans to recruit more people. Happy to be pointed in a direction to help out with this. Cheers Radley [Number%20of%20Staff] Staff Population.x…

And here.

Employee Headcount

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])))-CA…

It’s all about managing multiple dates in your fact tables like you have here.

I have recently created a video describing how to work with this.

See here…

How To Manage Multiple Date Calculations In Your Fact Tables - Advanced Power BI

I have another tutorial being release in the coming weeks around this specific case as well.

But the techniques are all the same.

You need a model that looks like this

Then a formula combination like this.

Current Staff = 
CALCULATE( COUNTROWS( 'Staff Population' ),
    FILTER( VALUES( 'Staff Population'[Start Date] ), 'Staff Population'[Start Date] <= MAX( Dates[Date] ) ),
    FILTER( VALUES( 'Staff Population'[End Date] ), OR( 'Staff Population'[End Date] >= MIN( Dates[Date] ), ISBLANK( 'Staff Population'[End Date] ) ) ) )

Check out the resources I’ve placed here and you’ll see exactly what needs to be done for this.

Thanks
Sam