Forum Discussion
Calculate Cumulative headcount numbers
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
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?
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))
9 Replies
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, 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
