Hiring, termination, Active Headcount based on historical data

[Anonymous]

Hi,

I am looking for a DAX measure to calculate above measures based on historical staff data at any period in time. Below is the sample of how data looks like.

id	start_date	end_date
1	07.02.2015	26.02.2015
1	27.02.2015	08.08.2015
1	09.12.2015
2	26.01.2019	03.03.2019
3	14.02.2012	27.08.2012
3	17.08.2017	12.01.2019
3	31.12.2020

Thanks

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create a calendar table.

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Date'[Date])
var _table=SELECTCOLUMNS('Table (2)',"start",[start_date],"end",IF([end_date]=BLANK(),TODAY(),[end_date]))
return
COUNTX(FILTER(_table,[start]<=_select&&[end]>=_select),[start])

3. Result.

What do you mean by "assisted with the logic behind calculating hire & termination count" is not very clear, is it a new field, can you describe it?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Active Headcount =
CALCULATE (
DISTINCTCOUNT ( Data[id] ),
FILTER (
Data,
OR ( Data[end_date] >= MIN ( Dates[Date] ), Data[end_date] = BLANK () )
&& Data[start_date] <= MAX ( Dates[Date] )
)
)

https://www.dropbox.com/s/l9uhw7owv0z5xjg/kunalv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_KimThanks,  I tried modifying the formula to this giving active employee at any point in time based on date filter:

Active Headcount =

CALCULATE (

DISTINCTCOUNT ( Data[id] ),

FILTER (

Data,

OR ( Data[end_date] >= MIN ( Dates[Date] ), Data[end_date] = BLANK () )

&& Data[start_date] <= MAX ( Dates[Date] )

), REMOVEFILTERS(Dates)

)

[Jihwan_Kim]

Hi, @Anonymous 

Sorry, I am not sure whether your response is a question.

[Anonymous]

@Jihwan_Kim Also, if you could assist with the logic behind calculating hire & termination count . The situation looks like for event in progress scenario. Many thanks.