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

     

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity 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's avatar
v-alq-msft
Icon for Community Support rankCommunity 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