Cumulative/Running Total with a Spin?

Firstly, remove the relationships with the start and end date columns. All those will do is filter to just those employees who started / finished in the given month.

You can then create a headcount measure like

Headcount =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( Staff ),
        Staff[Start Date] <= MaxDate
            && (
                Staff[End Date] > MaxDate
                    || ISBLANK ( Staff[End Date] )
            )
    )
RETURN
    Result

Hi Walt1010 ,

The suggested solution should help in tracking the number of current employees over time. If you need any adjustments or have questions, please let us know.

 

Also, I wanted to follow up to see if the solution shared by johnt75  worked for you. If you need more clarity or adjustments, don’t hesitate to reach out. 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

Share some data and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

Hi Walt1010 ,
To calculate and visualize the number of active employees over time, you need a measure that dynamically evaluates whether each employee is active on a given date. This involves checking if the employee's start date is on or before the current date in the context of the visual, and their end date is either blank (still employed) or after that date. Since you’re working with a Date table and a personnel table containing start and end dates, the best approach is to use a DAX measure with CALCULATE and FILTER to evaluate these conditions for each date on the axis. This measure can then be used in a line or area chart with the date field on the X-axis, providing a running total of current employees per day, without needing a traditional cumulative sum.

Current Employees =
CALCULATE(
    COUNTROWS(Staff),
    FILTER(
        Staff,
        Staff[Start_Date] <= MAX('Date'[Date]) &&
        (ISBLANK(Staff[End_Date]) || Staff[End_Date] >= MAX('Date'[Date]))
    )
)

Hi Walt1010 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

 

Thanks..

 

Hi Walt1010 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Hi Walt1010 ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.