Forum Discussion

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 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:

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

New Member

6 years ago

v-alq-msft

Community Support

6 years ago

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.

v-alq-msft
Community Support
6 years ago
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
Anonymous
Not applicable
6 years ago
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
dupreem
Frequent Visitor
6 years ago
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!