Headcount/inventory over time from a transaction table

[jtsmit7]

Hi all,

I am currently working on an HR Report that is meant to track headcount at our company.  We pull this data from UltiPro, and it comes out in a transaction table.  That is, every time a new employee gets hired, they get a new row.  Every time a current employee leaves, they get a new row to terminate them.  Every time a current employee gets a new salary, division, etc, they get a new row as well. 

We currently have the capabilities to extract the most recent transactions to create accurate displays of the current headcount of the company, but now would like to begin to track them over time, ideally in monthly buckets starting at the first of each month.

Here's an example of how the data looks:

Employee ID	Effective Date	Employee Status
1	1/1/20	Active
2	1/1/20	Active
3	2/1/20	Active
4	2/1/20	Active
1	2/15/20	Terminated
2	3/1/20	Active
1	4/1/20	Active

From this data we would want the headcount to be: Jan = 2, Feb = 4, Mar = 3, Apr = 4

We are particularly having trouble with the fact that almost all employees have multiple entries for their employee ID, as they get a new row almost every year as salaries, managers, etc. change.

Let me know of any possible feedback/solutions 🙂

Thanks in advance,

JS

[v-alq-msft · Accepted solution]

Hi, @jtsmit7 

Based on your description, I created data to reproduce your scenario.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

Employee ID(a calculated table):

Employee ID = DISTINCT('Table'[Employee ID])

There is a many-to-one relationship between 'Table' and 'Calendar'.

You may create two measures as follows.

IsActive = 
var _date = SELECTEDVALUE('Calendar'[Date])
var _id = SELECTEDVALUE('Employee ID'[Employee ID])
var _result = 
LOOKUPVALUE(
          'Table'[Empolyee Status],
          'Table'[Effective Date],
          CALCULATE(
              MAX('Table'[Effective Date]),
              FILTER(
                  ALL('Table'),
                  'Table'[Employee ID] = _id&&
                  'Table'[Effective Date]<=_date
              )
          )
)
return
IF(
  _result = "Active",
  1,
  IF(
      _result = "Terminated",
      0
  )
)

Count = 
SUMX(
    'Employee ID',
    [IsActive]
)

Results:

Best Regards

Allan

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

[jtsmit7]

Hi Allan,

I am currently getting an error when running the IsActive measure code.  "A table of multiple values was supplied where a single value was expected"

Here's my code:

IsActive =

var _date = SELECTEDVALUE('Calendar'[Date])

var _id = SELECTEDVALUE('Employee ID'[Employee ID])

var _result =

LOOKUPVALUE(

          'Employee Status Dates'[Employee Status Code],

            'Employee Status Dates'[Effective Date],           

            CALCULATE(

              MAX('Employee Status Dates'[Effective Date]),

              FILTER(

                  ALL('Employee Status Dates'),

                  'Employee Status Dates'[Employee Number] = _id&&

                  'Employee Status Dates'[Effective Date]<=_date

              )

          )

)

return

IF(

  _result = "A" || "T" || "S",

  1,

  IF(

      _result = "T",

      0

  )

)

I've been struggling to find the error.

Thanks,

JS

[jtsmit7]

@v-alq-msft 

[v-alq-msft]

Hi, @jtsmit7 

You may try the following measure.

IsActive =
VAR _date =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _id =
    SELECTEDVALUE ( 'Employee ID'[Employee ID] )
VAR _result =
    CALCULATETABLE (
        DISTINCT ( 'Employee Status Dates'[Employee Status Code] ),
        FILTER (
            ALL ( 'Employee Status Dates' ),
            'Employee ID'[Employee ID] = _id
                && 'Employee Status Dates'[Effective Date]
                    = CALCULATE (
                        MAX ( 'Employee Status Dates'[Effective Date] ),
                        FILTER (
                            ALL ( 'Employee Status Dates' ),
                            'Employee Status Dates'[Employee Number] = _id
                                && 'Employee Status Dates'[Effective Date] <= _date
                        )
                    )
        )
    )
RETURN
    IF (
        "A" IN _result
            || "S" IN _result
            || "T" IN _result,
        1,
        IF ( "T" IN _result, 0 )
    )

Best Regards

Allan

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

[dupreem]

Hello @v-alq-msft,

I believe this measure may be able to help with what I am trying to do as well. However, I am receiving the following error:
Fields that need to be fixed
Something's wrong with one or more fields: (Employee Status Dates)
IsActive: A single value for column 'Employee ID' in table 'Employee ID' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Any feedback is greatly appreciated!

[Anonymous]

Hi @v-alq-msft ,

Original poster here, just working with a new account now.  Sorry for the delayed response, we've had to sideline this project as we've been working on our COVID-19 reporting.

I've run this formula, but we're unable to show it across a line chart.  I get the 'ran out of usable memory' error every time I try to visualize the trend over time.  I believe it is due to the large size of the actual data compared to the small sample that was used here to get it effectively.  Do you have any ideas on how we would be able to show this/ restructure the data to be able to visualize the trend of our total heacount over time?

-JS

[v-alq-msft]

Hi, @jtsmit7 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[amitchandak]

@jtsmit7 

How do know the end/termination date of the employee column?

If you have that you can use :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Greg_Deckler]

Create an Employee table and have a Start Date of the MIN date where they are Active. Also have an End Date for the Termination date. Then you can use something like Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364