Staff Population

DAX DAX Calculations
1

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
Number of Staff.png1176×508 29.7 KB
Staff Population.xlsx (16.5 KB)

2

Have you had a chance to work through this workshop here as this covers many of these calculations for HR data.

As with anything here the data matters a lot.

So with your data you have to manage multiple dates…very doable.

This is where the ‘events is progress’ pattern comes into play. See below for the exact technique to use from a modelling and DAX formula perspective.

This is a different scenario exampled above but the technique is exactly the same.

I’ve worked on something briefly here to show you.

Model here, notice the inactive relationships…

image

I had to have a play around with a few ideas but this is what I came up with.

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

You’ll see I had to adjust a few things but the idea and technique is the same.

image
image631×589 17.7 KB

I did a quick audit and it looks close…I think.

You’ll have to work through and make sure, but hopefully you get the idea and can work on it from here.

Attached
Staff Popn.pbix (138.2 KB)

Sam

1 Like