Measure for Average Head Count

[Anonymous]

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.

[vapid128]

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])