I'm not sure I follow you: so your datasource has a start and end date but for active colleagues the end date is null but you need to show just the active colleagues?
If this is correct then a simple calc of:
The following will enable you to filter the set:
If DateDiff('day',[Start Date],(If [End Date] Is Null Then [Start Date]-1 Else [End Date] End)) > 1 Then 'Out' Else 'In' End
And this should allow you to count the number of active employees:
If DateDiff('day',[Start Date],(If [End Date] Is Null Then [Start Date]-1 Else [End Date] End)) > 1 Then 0 Else 1 End
What I am doing here is if the end date is null, then assign the date value of the date before the start date
In order to show each year and month, along with how many active employees there were, you are going to have to do is create a Unified (Or Master) date column. That way you have a date field that is not dependent on your start or end dates.
The easiest (And most flexible) way to do this is to create a table in your Database with a list of Dates, and then do a Cross Join to it. You can do this using Custom SQL, or a View, I prefer that latter if you are using SQL.
Something like that....
If you are using Excel, than please refer here
If neither of these method work, please let me know. There is a third method using Data Blending that would work as well. Though there are some limitations to that.
Thanks for the responses Steve and Rody, it jumpstarted my brain to understand what I need to do, with setting up a separate "Date Master" table at the granularity I need and drive the dates from there with the cross-join.
I am having a similar problem and I saw your response on creating a View for an SQL data source. Can you elaborate on that? I have a similar, if not more complicated, problem as Chris. I have 1 data source and have created a master date file in Excel. I do need to display the number of actives and terminated employees based on the available hire and term dates, no status column for inactive or actives.
Hope you can help. Thank you!