Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure for Average Head Count

Hello this is only my 2nd time posting to the forum.  I need assistance with some DAX measures so that I can determine Turnover % for an HR Power BI I have been tasked to complete.

I have managed to write the following measure to pull the amount of current employees:

VAR selectedDate = MAX('HR Date Range'[Date])

RETURN

SUMX('StaffTurnoverTable',
VAR employeeStartDate = [Hire_Date]
VAR employeeEndDate = [TermDate]
RETURN IF(employeeStartDate<= selectedDate &&
OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK()
),1,0)
)
This measure does pull the correct number of current employees but I need to have additional measures for the following:
1. Filtered by month/fiscal year (I have a date table with the fiscal month)
&
2. Average # of current employees for the fiscal year based on the numbers generated by the above measure.
If I need to provide additional information please let me know and thanks in advance for any help or suggestions with this.
 

1 Reply

  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    OK , I SEE THIS FOR ALL DAYS , AND NO ONE ANSWER.

     

    Creating only a measure is not a good idea for this case.

     

    Creating another table would make it work.

     

    Table22 = GENERATE('HR Date Range',GENERATESERIES(int('HR Date Range'[Hire_Date]),int('HR Date Range'[TermDate])-1))

     

    It will have a new colnum "value", it is your date colnum.

    your HC will be measure

    HC=DISTINCTCOUNT(table22[StaffID])