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 

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's avatar
Murali777
Helper III
4 years ago

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's avatar
    Anonymous
    Not applicable
    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