Forum Discussion

StaceyG's avatar
StaceyG
Helper I
5 years ago
Solved

Running headcount, current staff plus requisitions

I am trying to create a bar chart showing quarterly headcount projections.  I have a table which combines both curent staff and open requisitions.  I have the hire date for current staff and the target hire date for requisitions in the same [Hire Date] field.  I also have a designation on each row of "Budgeted" or "Not Budgeted" in the [Budget Status] column.  I'm struggling with how to get a running total of projected staff for each quarter.  It should be current staff plus requisitions where the [Hire Date] is <= quarter end (3/31/21, 6/30/21, 9/30/21, 12/31/21).  Would any of you geniuses be able to help me with this?

 

 

Thanks!

 

Stacey

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Hi StaceyG,

     

    Try this measure:

     

    Running Headcount =
CALCULATE (
    SUM ( Headcount[Count] ),
    FILTER (
        ALLSELECTED ( Headcount ),
        Headcount[Hire Date] <= MAX ( Headcount[Hire Date] )
    )
)

     

    Sample data:

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Hi StaceyG,

     

    Try this measure:

     

    Running Headcount =
CALCULATE (
    SUM ( Headcount[Count] ),
    FILTER (
        ALLSELECTED ( Headcount ),
        Headcount[Hire Date] <= MAX ( Headcount[Hire Date] )
    )
)

     

    Sample data: