Running Total Past 12 Months Headcount

[yaman123]

Hi all, 

Looking for some help. I have the below measure to calculate the headcount. I am looking to create a new measure to give me a running total for the past 12 months for headcount. 

TIA 

Yasir

Employee Count =
VAR selectedDate = MAX('Date'[Date])
RETURN
SUMX('Table1',
VAR employeeStartDate = [DATE_OF_EMPLOYMENT]
VAR employeeEndDate = [DATE_OF_LEAVING]
RETURN IF(employeeStartDate <= selectedDate &&
OR(employeeEndDate >= selectedDate, employeeEndDate=BLANK()
),1,0)
)

[amitchandak · Accepted solution]

@yaman123 , Create current employee like

example measure

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Then avg like
calculate(averageX(values(Date[Month -Year]), [Current Employees]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

[yaman123]

Thanks