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))
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.
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
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