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

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