Forum Discussion

Karla_hernandez's avatar
Karla_hernandez
New Member
2 years ago

Need help with Position Changes DAX formula

I have a historical table that brings in employee data each month. This means the same employee can have multiple records in the table. I am trying to write a DAX measure that looks at the employee's latest position record. I would like to use this measure to create a position headcount visual. The measure below seems to get me the right headcount for the selected position but it does not allow me to use any other filters in the table

 

Team Lead Position Headcount =
VAR SelectedPosition = "IO0037- TEAM LEAD"
RETURN
CALCULATE(
    COUNTROWS(
        SUMMARIZE(
            FILTER(
                ALL('Employee List_Main Table'),
                'Employee List_Main Table'[Position] = SelectedPosition
                    && 'Employee List_Main Table'[Position Start Date] <= MAX('Calendar'[Date])
                    && ISBLANK('Employee List_Main Table'[Termination Date])
                    && 'Employee List_Main Table'[Hire Date] <= MAX('Calendar'[Date])
                    && 'Employee List_Main Table'[ReHire Date] <= MAX('Calendar'[Date])
            ),
            'Employee List_Main Table'[Employee Code]
        )
    )
)
 
Ideally, I would like to have a measure that allows me to pull multiple positions in one column chart and allows me to use the table filters
Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Karla_hernandez there are so many posts on doing the head count. did you take the time to search for it and see if existing solution will work for you?

    • Karla_hernandez's avatar
      Karla_hernandez
      New Member
      2 years ago

      Yes I have been searching for a solution for over a week now. I have a seperate headcount report that calculates headcount based on an employees single record

       

      In this case I want to account for any position changes an employee has had and only want to include in the count the employees latest record. For example, In june an employee may be in X position and they should be included in the X position headcount for June but in July that employee moved to Y position and should now be included in the Y positon for July headcount.