Cummulative Running Total

[Murali777]

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

[ryan_mayu]

@Murali777 

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

[Murali777]

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.

[ryan_mayu]

@Murali777 

still confused about this.

I can understand that by the end of 1999, the two empoyee of 1997 have left. However, the empcount for 1999 is 1. Why not 1+2+1-2. What's the meaning of empcount column?

[Murali777]

Employee count is the count of employee id. I have created one Rownumber column =1 in my model, based on the department i aggregating the Rownumber column as EmpCount.

[Anonymous · Accepted solution]

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