running counts over time

Question

I have a staff employee list, with a name, start date and end date as three columns. If the staff member is currently employed the end date is blank.

I'm trying to create a graph that shows the additions and subtractions over time. A line chart or waterfall chart showing the year across the x axis with a cumulative staff count (up or down) over time showing dips and gains.

What is the measure to get a running count of employees against time?

I imagine it is something like, add 1 if the employee end date is blank, but if the end date isn't blank then -1. And then, how to show this as a cumulative total against time?

New to this, so any help would be appreciated!

dedelman_clng · Answer

HI&nbsp;michaelpop&nbsp; - first, make sure you have a Date table (CALENDARAUTO() works well), and make a relationship to Hire Date
&nbsp;
Then create a measure
&nbsp;
Running Total =
CALCULATE (
    COUNTROWS (
        FILTER (
            Emp,
            Emp[End Date] &gt; MAX ( DateTab[Date] )
                || ISBLANK ( Emp[End Date] )
        )
    ),
    FILTER ( ALL ( DateTab ), DateTab[Date] &lt;= MAX ( DateTab[Date] ) )
)

&nbsp;
And your graph
&nbsp;

&nbsp;
Hope this helps
David

michaelpop · Answer

Thanks David,&nbsp;I've been looking into this and would like to show graphically the 'losses' and 'gains' over time with a current total for the year. This might show for example like a 2016 +2 (bar) but also minus 2 (bar below) leaving a running total (line) of 3. 2017 would read +1 aquisitions for a total of 4 staff. etc.&nbsp;Is this possible?&nbsp;example:Name&nbsp; &nbsp; &nbsp; &nbsp;StartDate&nbsp; &nbsp; EndDatePersonA&nbsp; &nbsp;1/01/2000&nbsp; &nbsp;nullPersonB&nbsp; &nbsp;1/01/2001&nbsp; &nbsp;nullPersonC&nbsp; &nbsp;1/01/2001&nbsp; &nbsp;nullPersonD&nbsp; &nbsp;1/01/2002&nbsp; &nbsp;30/05/2015PersonE&nbsp; &nbsp;1/03/2016&nbsp; &nbsp;30/05/2016PersonF&nbsp; &nbsp;1/01/2016&nbsp; &nbsp;30/07/2016PersonG&nbsp; &nbsp;1/06/2017&nbsp; &nbsp;nullPersonH&nbsp; 1/06/2018&nbsp; &nbsp;1/06/2019PersonI&nbsp; &nbsp;1/09/2018&nbsp; &nbsp;nullPersonJ&nbsp; &nbsp;1/01/2020&nbsp; &nbsp;null

dedelman_clng · Answer

Hi&nbsp;michaelpop&nbsp; -&nbsp;
&nbsp;
First make sure your Date table has Year and Month as columns, and create an INACTIVE relationship between it and End Date

&nbsp;
Then the "New This Period" and "Gone This Period" calculations are
&nbsp;
New This Pd = CALCULATE(COUNTA(Emp[Unique ID]))

Gone this Pd = 
    -1*(CALCULATE( COUNTA(Emp[Unique ID]), USERELATIONSHIP(Emp[End Date], DateTab[Date])))
&nbsp;
Put them as columns on a "Stacked Column+Line" chart, with running total as the line
&nbsp;
Year View