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 ne...

Need Help

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: