Count of employees for each date based on date ranges?

[Anonymous]

I have an assignment table which includes a unique assignment ID and associated with an employee and their start date and end date for that assignment. I have an employee table which is associated with the assignment table via the unique employee IDs. I also have a calendar dimension table. Basically what I want to do is create a line chart which has the x axis as week and the y axis as count of employee IDs. I want the count of employee IDs to only count the unassigned employees, and this would be based on the assignment start and end dates in the assignment table. Basically, if the week is outside that range, that employee would be counted for that week, but if the week is within that range, the employee would not be counted. Is this possible to do?

[v-sihou-msft]

@Anonymous

According to your description, you want to count  the "Active" employees within a date range. Right?

For this kind of SCD scenario, you can try formula like:

Active Promotions:=CALCULATE(COUNTROWS(DimEmployee), 
    FILTER(DimEmployee, (DimEmployee[StartDate] <= LASTDATE(DimDate[Datekey]) 
        && DimEmployee[EndDate>= FIRSTDATE(DimDate[Datekey]))))

For more details, please refer to links below:

https://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbell/

http://geekswithblogs.net/darrengosbell/archive/2014/04/09/dax-ndash-joining-to-a-slowly-changing-dimension.aspx

http://blog.gbrueckl.at/2012/02/handling-scd2-dimensions-and-facts-with-powerpivot/

Regards,