running counts over time

[michaelpop]

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]

HI @michaelpop  - first, make sure you have a Date table (CALENDARAUTO() works well), and make a relationship to Hire Date

Then create a measure

Running Total =
CALCULATE (
    COUNTROWS (
        FILTER (
            Emp,
            Emp[End Date] > MAX ( DateTab[Date] )
                || ISBLANK ( Emp[End Date] )
        )
    ),
    FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)

And your graph

Hope this helps

David

[michaelpop]

Thanks David,

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.

Is this possible?

example:

Name       StartDate    EndDate
PersonA   1/01/2000   null
PersonB   1/01/2001   null
PersonC   1/01/2001   null
PersonD   1/01/2002   30/05/2015
PersonE   1/03/2016   30/05/2016
PersonF   1/01/2016   30/07/2016
PersonG   1/06/2017   null
PersonH  1/06/2018   1/06/2019
PersonI   1/09/2018   null
PersonJ   1/01/2020   null

[dedelman_clng]

Hi @michaelpop  - 

First make sure your Date table has Year and Month as columns, and create an INACTIVE relationship between it and End Date

Then the "New This Period" and "Gone This Period" calculations are

New This Pd = CALCULATE(COUNTA(Emp[Unique ID]))

Gone this Pd = 
    -1*(CALCULATE( COUNTA(Emp[Unique ID]), USERELATIONSHIP(Emp[End Date], DateTab[Date])))

Put them as columns on a "Stacked Column+Line" chart, with running total as the line

Year View

Month view

Hope this helps

David