Average headcount over a period

[Anonymous]

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

[amitchandak]

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

[Anonymous]

@amitchandak 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!

[v-yuta-msft]

@Anonymous ,

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] >= Range_Start
                && Table[lastDayWithClient] <= 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.

[Anonymous]

@v-yuta-msft 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

[v-yuta-msft]

@Anonymous ,

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] >= Range_Start
                && Table[lastDayWithClient] <= 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.

[Anonymous]

@v-yuta-msft Yes, I need the results grouped by supname, but this is not my concern right now. What I mean is, I need to be able to add up all the days that all employees are working. So, one person could have just one row in my table, and most do. However, let's say our date range is 1/1/19 to 1/30/19, and the employee had a firstDayWithClient of 1/10/19 and a lastDayWithClient of 1/15/19. Then this person would only have one row, but should be counted as 6 since they were working for 6 days within our date range. Unless I'm mistaken, the method you provided would only count the person once since they would only have one row of data with both their first and last days on it. I hope I'm explaining this well enough, but please tell me if I'm not, and thanks again for your help!

[v-yuta-msft]

@Anonymous ,

Try using COUNTROWS() and check if it can meet your requiremet:

Result =
VAR Range_Start =
    MINX ( ALLSELECTED ( Date ), Date[Date] )
VAR Range_End =
    MAXX ( ALLSELECTED ( Date ), Date[Date] )
RETURN
    COUNTROWS (
        FILTER (
            ALLEXCEPT ( Table, Table[supName] ),
            Table[firstDayWithClient] >= Range_Start
                && Table[lastDayWithClient] <= Range_End
        )
    )
        / 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.

[Anonymous]

@v-yuta-msft This didn't quite work since I don't need a count of rows, rather a count of the number of days an employee was working . So an employee might have just one row but be working for 30 days within the time period I've specified, and so I'd need this person to sum to 30, not 1. The key is that if someone works from say 1/1/19 to 1/13/19, that person will only have one row, but needs to be counted for every day between his start and end dates which was also between whatever dates I set as my filter. I have figured out a static solution for now which does not give me control of the date range, so if you think you have a solution to the original problem please let me know, but otherwise thank you for your help!

[v-yuta-msft]

@Anonymous ,

Could you please share sample data and give the expected result column in your sample data?

Regards,

Jimmy Tao