Forum Discussion

jtsmit7's avatar
jtsmit7
New Member
6 years ago
Solved

Headcount/inventory over time from a transaction table

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

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

9 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      jtsmit7
      New Member
      6 years ago

      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