Forum Discussion
Calculate Cumulative headcount numbers
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))
Community ChampionAnonymous 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.
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))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))