cancel
Showing results for
Search instead for
Did you mean:
Post Patron

## Running Total Past 12 Months Headcount

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

1 ACCEPTED SOLUTION
Super User

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

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

2 REPLIES 2
Super User

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

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

Post Patron

Thanks

## Helpful resources

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors