Calculate headcount question

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

1 Like

Hi there,

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

Also see here for some other solutions

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

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.


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.

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

You need two inactive relationships.

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.

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