Calculate Cumulative headcount numbers

[Anonymous]

Hi there, need some assistance with calculating cumulative headcount.

I have a data file with a starting headcount for the beginning of the year and then all year to date hires and leavers in separate data files, all linked to a date lookup table so i know what period each hire or termination occured.

I have added in a measure for cumulative headcount as shown below

Cumulative HC = (P1_Opening[P1 Opening HC]+[Total Hires]-[Total Leavers])

I have created a chart for this (simple line chart) with Period in the Axis field and Cumulative HC in values and it plots the line. The first period works fine but the subsequent periods do not work.

As you can see in the visual P2 adds the net position for P2 to the starting headcount number but doesnt take into account the P1 net position so it isnt really cumulative

Can you offer any advice to fix this please?

[v-janeyg-msft]

Hi, @Anonymous 

I agree with  @Greg_Deckler .You need to rely on a date column to calculate the cumulative. If you still need support, please upload some insensitive data samples and expected output, so we can help you soon.

Best Regards,

Community Support Team _ Janey

[Greg_Deckler]

@Anonymous Would need more information like your Total Hires and Total Leavers measure formulas. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi @Greg_Deckler , i have added in the visual i am working on so hopefully this will offer some more clarity and re-edited the post too. Hope you can help but let me know if you need anything else. Thanks

[Greg_Deckler]

@Anonymous I still don't see the formula for Total Hires. Here is what I would expect to see in a Running Total Hires measure:

Running Total Hires =
  VAR __Date = MAX('Table'[Date])
RETURN
  COUNTROWS(FILTER(ALL('Table'),[Date] <= __Date && [New Hire] = 1))

[Anonymous]

Hi @Greg_Deckler ,

Current measures i created are as follows

Cumulative HC = (P1_Opening[P1 Opening HC]+[Total Hires]-[Total Leavers])

Total Leavers = COUNT(Terminations[Employee ID])

Total Hires = COUNT(New_Hires[Employee ID])

Is the formula you have suggested still relevant?

Thanks

[Greg_Deckler]

@Anonymous Yes. What is currently going on is that each point in the line is calculated for a specific period like P3 FY22. Now, assuming that the dates are related to all of your other tables, when you calculate COUNT of Terminations or New_Hires, you are only catching the employees terminated or hired during that period. Thus, you need to override the context created by P3 FY22 and implement your own context. That is the purpose of the FILTER with the ALL. Effectively what this is saying is that "give me all hires prior to or equal to my current date. But, now that I know a little more about your data model, it would be something like:

Running Total Hires =
  VAR __Date = MAX('Table'[Date])
RETURN
  COUNTROWS(FILTER(ALL('New_Hires'),[Date] <= __Date))

[Anonymous]

Thanks @Greg_Deckler 
i have attempted the formula suggested but doesnt seem to work for me as not splitting by the individual periods

Running Total Hires = VAR __Date = MAX(New_Hires[Effective Date]) RETURN COUNTROWS(FILTER(ALL(New_Hires),TODAY() <= __Date))

Do i need to insert another date in this formula where i have made the text bold?

[Greg_Deckler · Accepted solution]

@Anonymous Why are you using TODAY? Where you have TODAY, this needs to be the termination date of your person.

Running Total Hires = VAR __Date = MAX(New_Hires[Effective Date]) RETURN COUNTROWS(FILTER(ALL(New_Hires),[Effective Date] <= __Date))

[Anonymous]

thank you so much @Greg_Deckler , this now works!!
muchos gracias