We often get asked for how to calculate the monthly number of active users or “how many employees are on payroll each month”. Its the classis HR headcount problem no matter how its phrased and is super simple to handle.
To start, we have our table that has employee name, their start date, and an end date (null if they are still active).
First thing we should do is add “end dates” to the active employees. Easiest way to do this is via the Coalesce() function as shown below.
From here we need a Date Dimension table. This should have all the months we wish to include in our analysis. This Date dimension can either be done with Custom SQL or via the Input Table / CSV or maybe you even have one in your warehouse already. Here is a blog post from my colleague Yuri on how to get this done a few different ways, the most important thing here is that this Date Dimension table must be made on the same connection as the employee table.
Now that we have our 2 sources, we create a third - join - table using 2 join keys which should “fan out” our table creating a row for each month the employee was part of the company (each month the user was a subscriber, each week a person visited a store etc.).
From here the bulk of the work is done and you can create your analysis as you wish. One example os a simple chart plotting number of people over time like this
Hope this helps you with modeling count of things over time when there is a start and end date.