Cumulative count of employees

[Anonymous]

I have an assignment table where each record includes an assignment start date, assignment end date and an employee ID. Each assignment is associated with one employee. Basically, I want to create a line chart which displays the count of employees not on assignments for each week, based on the week during which each employee ends their current assignment. Basically if an employee has an assignment end date next week, they would be included in the count for next week and all subsequent weeks. This will be a future forecast calendar and there will be no assignments starting after the end of the current assignment. How would this be done?

[austinsense]

Alright, here's how it's done ...

=COUNTROWS(
     FILTER( AssignmentsTable,
                        // if you're looking at Mar-16 then this will show all assignments starting before or on 3/1/16
                        AssignmentTable[StartDate] <= MIN(Calendar[Date]) &&
                        // if you're looking at Mar-16 then this will include all assignments ending after 3/31/16
                        AssignmentTable[EndDate] > MAX(Calendar[Date])
     )
)

You may have to play around with signs (<, >, <=, >=) and the MIN/MAX but this is the basic idea.  You definitely need a calendar table for this.

[v-ljerr-msft]

Hi @Anonymous,

Could you post some sample data with your expected result in this case?

Regards