Average headcount for a given period

Hi Paul,

I appreciate the reply!

While I have found -a- solution to this issue, I wouldn't mind a better one. 🙂 This solution is "imperfect" because it relies on creating what is potentially millions of record rows. However, it does provide accurate and easy to manage calculations.

To summerize the issue:

I have a set of data that includes these relevant fields: Start Date, End Date and ID

I'm attempting to count average headcount over any given period. My results might look like;

The proper way to calculate this would be the forumula below:

Sum number of days each record was active in a period / Number of days in the period

Here is an Example: Given only the below table, If I wanted to calculate the average headcount in June 2018, I would get 2.3

12+16+12+23+5 = 68

68/29  = 2.3

Where 29 is the number of days in the month of June 2018.

------------------------------

Here is my current imperfect solution:

- Create unique rows for every date a record is active and expand the table.

- Create an Active relationship between this table and a date table

From here, it is a simple measure to create a table which calculates your average headcount:

Average Headcount = COUNTROWS(Query2) / COUNTROWS('Date')

I only ultimately worry about the number of rows this creates. I don't have a lot of experience with the platform, so I'm not sure whether its going to be able to handle a number like 10 million rows, I may have to limit my date ranges to ~2-3 years to compensate

Thanks for everyone's time. 🙂