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

 

    • v-alq-msft's avatar
      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's avatar
        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