Forum Discussion

Murali777's avatar
Murali777
Helper III
4 years ago
Solved

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.   ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

ryan_mayu's avatar
ryan_mayu
Super User
4 years ago

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