Average headcount over a period

Question

Hi everyone, I was hoping someone could help me with this problem.

I have a dataset like below, and I'm trying to find the average number of people working over a given period of time, defined by Total number of people working each day/Total number of days in the period. Each row is a person. A person can be on multiple rows, but only with a different departmentCommonName, and with first and last days which do not overlap. I'll be breaking this up by departmentCommonName anyway, so that part shouldn't matter much I don't think.

Someone is considered working if their firstDayWithClient is before whatever end date we have, and their lastDayWithClient is either blank or is after whatever start date we have. I can calculate however many people we have working at all during a period of time, but I'm trying to find the average as defined above. Any ideas?

Thank you

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
Suppose you have another Date table and the silcer is created on the Date column in that table, then you could create a measure using dax like pattern below:
Result =
VAR Range_Start =
    MINX ( ALLSELECTED ( Date ), Date[Date] )
VAR Range_End =
    MAXX ( ALLSELECTED ( Date ), Date[Date] )
RETURN
    COUNTX (
        FILTER (
            Table,
            Table[firstDayWithClient] &gt;= Range_Start
                &amp;&amp; Table[lastDayWithClient] &lt;= Range_End
        ),
        Table[supName]
    )
        / DATEDIFF ( Range_Start, Range_End, DAY )

Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
If my understanding is correct, you need the result grouped by supname, right? If so, just add an ALLEXCEPT() function in the filter as below:
Result =
VAR Range_Start =
    MINX ( ALLSELECTED ( Date ), Date[Date] )
VAR Range_End =
    MAXX ( ALLSELECTED ( Date ), Date[Date] )
RETURN
    COUNTX (
        FILTER (
            ALLEXCEPT ( Table, Table[supName] ),
            Table[firstDayWithClient] &gt;= Range_Start
                &amp;&amp; Table[lastDayWithClient] &lt;= Range_End
        ),
        Table[supName]
    )
        / DATEDIFF ( Range_Start, Range_End, DAY )

Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak · Answer

&nbsp;Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.Thanks

anonymous · Answer

amitchandak&nbsp;Thanks for your reply. I don't think I can supply a pbix file, but here's an example of what I mean. Using the data above, if we set our date range to say 6/12/2017 to 6/13/2017, then I want to find the average number of people working in that period of time. To do so, I have to add up the number of people working every day first. So, I count the number of people working 6/12/2017 which is just one. Then, count the number of people working on 6/13/2017, which is also just 1. At this point I am at 2 since I added the previous two days where only one person was working during those days. If I then divided by 2 (the number of days in my period), I'd get an average of 1 person working during this period. If it's any clarification, firstDayWithClient marks the day an employee started working, and lastDayWithClient marks the last day they worked. The time in between these two is the time period in which they were working. Thank you again!

anonymous · Answer

v-yuta-msft&nbsp;Thanks for your response! The solution you provided looks very promising, but doesn't seem to get the job done. I think the problem is with the COUNTX part, since this would only count each person once. However, I want to count each person for each day they worked. So, if someone was working 5 days within the date interval, they should count as 5, not 1, even though it's the same person. For example, if two people have a firstDayWithClient of 1/1/19, and a lastDayWithClient of 1/10/19, they are working for 20 days (10 days each), and should be counted as 20. If this was also our date range, then we'd divide by 10 (number of days) to get an average headcount of 2, which is accurate. Do you know how I can adjust your method to do that? Or perhaps what other method I could use? Thank you again

Forum Discussion

Average headcount over a period

9 Replies

Related content

Average Headcount

Average headcount DAX and unlinked calendar

Average headcount for a given period

Average Headcount Calculation

Headcount last year period