cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors