Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hiring, termination, Active Headcount based on historical data

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 ...
Need Help
Tips and Tricks
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago
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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.