Cumulative change in headcount by month

[Anonymous]

Hi I am trying to calculate a headcount forecast. I'am able to calculate the actual headcount for today. This is a calculated measure in my data. We want to calculate a headcount forecast based on the expected in- and outflow. As you can see in the provided example we expect to grow with 14,95 in August and 19,60 people in September. Therefore the cumulative headcount should be 914,95 in August and 934,55 in September.

The forecast headcount graph is build on a single measure, sum of inflow chance - sum of outflow chance and plotted on a date dimension table given the expected start and expected end date contract. 

Does anybody know how to achieve this? 

Thanks in advance!

[v-yulgu-msft]

Hi @Anonymous,

Have you worked it out? If yes, would you please mark corresponding reply as an answer so that more users can benefit from here? If you still have any question, please feel free to ask.

Regards,
Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft

I think we're almost there. So both the [Inlfow] and [Outflow] measure work as expected. The add up as they should do. But when I combine both measures and include actual headcount, the counting starts at the wrong moment. The actual headcount is a measure of the actual headcount for today. In this example 976. So the calculation should start from today instead of incorporating the months before today (154,25 in august). I expect that the actual headcount in October should be 976 +  growth in September.

Hopefully you are able to help me out. Thanks in advance.

Ronald

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Suppose there are two columns [inflow] and [outflow] in table 'Recruitment chance'. To get the forecast headcount, you could create measures like below:

running inflow measure =
CALCULATE (
    SUM ( 'Recruitment chance'[inflow] ),
    FILTER (
        ALL ( 'Recruitment chance' ),
        'Recruitment chance'[Date] <= MAX ( 'Datedim'[Date] )
    )
)

running outflow measure =
CALCULATE (
    SUM ( 'Recruitment chance'[outflow] ),
    FILTER (
        ALL ( 'Recruitment chance' ),
        'Recruitment chance'[Date] <= MAX ( 'Datedim'[Date] )
    )
)

Running Growth = [running inflow measure]-[running outflow measure]

forcast headcount = [actual headcount for today]+[Running Growth]

Here, 'Recruitment chance'[Date] is the date column that being based on to connect to table 'Datedim'.  [actual headcount for today] is a measure you mentioned above that calculates the actual headcount for today.

Then, in column chart, you can add 'Datedim'[Date] into Axis area and add measure [forcast headcount] into Values section.

Regards,
Yuliana Gu