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

 

Murali777's avatar
Murali777
Helper III
4 years ago

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