Forum Discussion
Cummulative Running Total
Hi,
I need to do the running total of the department employee count, but in the same time i need to remove the termination employee once he reaches the year. Please see the below example.
e.g) in year 1997, we need to consider that employee is active one, once he reached 1999, we need to remove from the running count value in 1999.
Kindly help any one of super hero!!!
Hi Murali777 ,
Could you please share some raw data in your employee table with us in order to provide you a suitable solution? Besides that, you can refer the following links to get the employee count over time.
Employee Headcount history changes
Headcount_4 = CALCULATE ( [HeadCount] FILTER ( Worker_Hist; Worker_Hist[Start Date] <= MAX ( Calendar[Date] ) && ( Worker_Hist[End Date] > MAX ( Calendar[Date] ) || ISBLANK ( Worker_Hist[End Date] ) ) ) )Total Number Of Staff Over Time - Power BI Insights
Optimizing HR Head Count DAX Measure with Power BI
Head Count = VAR FromDate = MIN('Date'[Date]) VAR ToDate = MAX('Date'[Date]) RETURN CALCULATE ( COUNTROWS( Employee ), Employee[HireDate] <= ToDate, Employee[LeaveDate] >= FromDate || ISBLANK(Employee[LeaveDate]), REMOVEFILTERS('Date') )Best Regards
5 Replies
some questions on your screenshot. What's the meaning of empcount? does that number including previous years employee?
if not including, Let's see department A, 1996 one employee, 1997, two employees ,totally three.
in 1999, why running count is one? shall we add the one employee in 1996? It's because the termination date is after 1999. Is the correct running count for 1999 is 2?
Column = VAR _date=date('Table'[Year],12,31) return sumx(FILTER('Table','Table'[Year]<=EARLIER('Table'[Year])&&'Table'[Department]=EARLIER('Table'[Department])&&'Table'[Termination]>=_date),'Table'[empcount])pls see the attachment below
Hi Ryan,
Thanks for your reply. In Year 1997, termination employee count is 2, so the running total in 1999, should me 3-2 = 1. Also could you pls arrive this solution in Measure.
