Calculate headcount question

[rita.carnes]

I have a table with team members and their allocation % to a Scrum Team with start and end dates. I need to calculate headcounts for each month for each Scrum Team. I am getting hung up on being able to figure out how to group by Scrum Team and then account for the dates. Each month the team member needed to have a start date in that month or earlier or a end date within the month or blank. I have added an image of 1 team and the data

[sam.mckay]

Hi there,

I have the exact solution for you in the tutorial below.

Total Number Of Staff Over Time - Power BI Insights

Also see here for some other solutions

How to calculate Headcount over time

Hi, I was trying to solve an issue in PowerBI, which I thought, should be an easy task. Unfortunately it isn´t - at least - not for me. After trying several approaches without any noticeable progress, I wanted to ask for your support, before it drives me crazy Here is the scenario: We have several Cost Centers in different locations. Each Cost Center has a Cost Center Lead and some Users Each employee (Lead and Users) have a defined Start and End Date Employees can change Cost Center…

Employee Headcount

Help please: I have been using the below formula for headcount - this appears to work correctly for top line number of colleagues - however when it is filtered down to team level it doesn’t give the correct headcount. I essentially need the exact number of employees who are with us on any given date. My headers on my data include: Date Joined Date Left Department Leaving code Description No of Colleagues = CALCULATE([Joining],FILTER(ALL(Dates[Date]),Dates[Date]<=AVERAGE(Dates[Date])))-CA…

[rita.carnes]

I need a running total so in your example it gives me only the count for that month meaning if someone starts in Jan I am not getting them in Feb. Feb should be anyone that started before Feb 28th with a end date blank before Feb 28th. I copied your to match my table columns.

Head Count = 
CALCULATE (COUNTROWS ('Employee Allocation to Scrum Team'),
	FILTER (VALUES ('Employee Allocation to Scrum Team'[Start Date] ),
               'Employee Allocation to Scrum Team'[Start Date] <=MAX(Dates[Date])),
	FILTER (VALUES ('Employee Allocation to Scrum Team'[End Date]), OR 
              ('Employee Allocation to Scrum Team'[End Date] >=MIN(Dates[Date]), 
              ISBLANK ('Employee Allocation to Scrum Team'[End Date] ))))

[sam.mckay]

Are you sure? I believe this should give you a running total as per the example tutorial.

If you have some images or a demo model of the issue that would clear things up.

Thanks

[rita.carnes]

Here is the images. As you can see I have a large amount of teams with Employees that start in Jan. They have no end date and those teams are not showing in Feb unless they had an employee with a start date in Feb. I need a monthly slicer on this report.

[sam.mckay]

Are you sure your relationships in your model are setup exactly as per the example?

You need two inactive relationships.

[rita.carnes]

Here is my data model.

I need to be able to get a headcount and a team rate from the them employee up to their allocated time to a scrum team. Then I need to calculate the costs on at the team layer by hour conversion of story points % from the done issues.

I don’t know how to match the connections from scrum team to issues to combine all the types of calculations I need to do to show project cost.

Not sure if you can blow up the image to see it.

[sam.mckay]

Ok, quickly I can see this isn’t setup correctly at all.

For this formula to work you need to get your setup in the model exactly as per the example with two inactive relationships from you date table to your fact table with the detail it in.

Please review the tutorial in detail.

Also this model in general is suboptimal. Please go through the advanced data transformations course module as soon as you can. All my best practices are contained within this course

Thanks
Sam