cancel
Showing results for
Search instead for
Did you mean:
Helper V

## 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 IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

2 REPLIES 2
Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper V

Thanks

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors