Forum Discussion

Sgbo's avatar
Sgbo
New Member
3 years ago
Solved

Rolling 12 Month Headcount

Hi,

 

I understand this is a well known topic, but I can't seem to find a way to show the headcount every month factoring in the terminations and hires. I also need to be able to separate out the hires by department.  I do not need just the counts of hires by month (I have that already figured out). I have tried solutions like: 

 
HC= CALCULATE(COUNTA(Query1[PBI ID]),FILTER(ALL(Query1),(Query1[Date Hired]>='Calendar'[Date])))- CALCULATE(COUNTA(Query1[PBI ID]),FILTER(ALL(Query1),(Query1[Last Day Worked]>='Calendar'[Date])))
 
AND
 
HC=CALCULATE(COUNT(Query1[PBI ID]),DATESBETWEEN('Query1'[Date Hired],MIn(Query1[Date Hired]),MAX('Query1'[Date Hired])))

 

Here is a sample data set along with the calendar table I used (=CALENDARAUTO()):

PBI IDDEPTARTMENTDate HiredLast Day Worked
B1

ACCT

11/15/2021 0:00 
B11FINA6/21/2021 0:00 
B111FINA8/13/2012 0:00 
B113FIN3/1/2021 0:00 
B114ACCT4/1/2014 0:00 
B115ACCT2/1/2022 0:00 
B116ACCT10/1/2021 0:00 
B117SALE9/1/2021 0:00 
B118SALE5/22/2017 0:00 
B119SALE10/17/2022 0:00 
B12SALE1/6/2020 0:0012/1/2022 0:00
B120SALE8/2/2010 0:00 
B121SALE12/6/2021 0:00 
B123HR1/1/2022 0:003/18/2022 0:00
B124HR11/8/2021 0:00 
B125HR7/1/2021 0:00 
B127HR12/15/2006 0:00 
B128HR4/9/2012 0:003/24/2022 0:00
B13HR11/9/2015 0:00 
B130HR5/16/2016 0:00 
B132HR7/6/2020 0:00 
B133MARK1/17/2022 0:00 
B134MARK5/16/2016 0:00 
B136MARK8/1/2016 0:00 
B139MARK10/1/2022 0:00 
B140MARK11/29/2021 0:00 
B145MARK10/22/2012 0:00 
B146MARK5/23/2022 0:00 
B147MARK4/1/2014 0:00 
B148ADMI7/1/2021 0:0012/25/2022 0:00
B15ADMI8/2/2021 0:00 
B151ADMI2/8/2016 0:008/22/2022 0:00
B153ADMI4/1/2014 0:006/24/2022 0:00
B154ADMI1/1/2022 0:00 
B155ADMI11/22/2021 0:003/15/2022 0:00
B156ADMI9/7/2021 0:00 
B158ADMI7/6/2021 0:00 
B159ACCT10/31/2016 0:00 
B16ACCT10/16/1987 0:00 
B160ACCT5/16/2016 0:00 
B165ACCT12/11/2017 0:00 
B167JANI1/17/2022 0:00 
B168JANI1/6/2020 0:0011/10/2022 0:00
B169JANI4/9/2012 0:007/1/2022 0:00
B17JANI12/13/2021 0:009/2/2022 0:00
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Sgbo ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create measures.

    Measure = IF(MAX('Table'[Last Day Worked])=BLANK(),TODAY(),MAX('Table'[Last Day Worked]))
    Measure 2 = var _selectedvalue=MAXX(ALLSELECTED('Table 2'[Date]),'Table 2'[Date])

var _persom=CALCULATE(COUNT('Table'[PBI ID]),FILTER(ALL('Table'),'Table'[Date Hired]<=_selectedvalue&&[Measure]>=_selectedvalue))
return _persom

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

2 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi there, not directly answering your question here, but just wanted to say, the more complex my HR report was the harder was it to maintain the approach of calculating HC in between the dates. May be you just create monthly views, so you can see who is a leaver, joiner, HC in a particular month row wise.

     

    Take a look at the second story in this article, i am always using this approach to avoid the headache.

     

    https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?trackingId=JyCX4ulgQfeWr%2Bce3tuxoQ%3D%3D

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Sgbo ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create measures.

    Measure = IF(MAX('Table'[Last Day Worked])=BLANK(),TODAY(),MAX('Table'[Last Day Worked]))
    Measure 2 = var _selectedvalue=MAXX(ALLSELECTED('Table 2'[Date]),'Table 2'[Date])

var _persom=CALCULATE(COUNT('Table'[PBI ID]),FILTER(ALL('Table'),'Table'[Date Hired]<=_selectedvalue&&[Measure]>=_selectedvalue))
return _persom

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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