Forum Discussion

michaelpop's avatar
michaelpop
Regular Visitor
6 years ago

running counts over time

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 addi...
Need Help
dedelman_clng's avatar
dedelman_clng
Community Champion
6 years ago

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's avatar
michaelpop
Regular Visitor
6 years ago

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's avatar
    dedelman_clng
    Community Champion
    6 years ago

    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