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.

 

idstart_dateend_date
107.02.201526.02.2015
127.02.201508.08.2015
109.12.2015 
226.01.201903.03.2019
314.02.201227.08.2012
317.08.201712.01.2019
331.12.2020 

 

Thanks

Need Help
Tips and Tricks

5 Replies

  • 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

    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.