Average Clients Per Hour, over a monthly timeframe

[Creative_tree88]

Hi all - trying to calculate the AVERAGE number of clients seen, per hour, over the course of a monthly timeframe (also yearly if possible).

I've attached a sample PBIX for you to help if possible, and show what I expect a little more clearly.  I've already used a formula which calculated the average number of clients on a daily basis (see PBIX attached), but I am lost as to then work out how this translates monthly, and if possible yearly.

Your help is much appreciated, as always.

Sample PBIX 

[Creative_tree88]

@Anonymous Thanks.  However, this is not what I need.  I need the average per hour of clients seen, when taken across the whole month.  The figure of 16.77 for Apr-14 is not the average per hour for Apr-14, the average per hour is 1.28.  So, for the whole of April-14, we saw (on average) 1.28 clients per hour.  This is what I need to work out.

Thanks for your time.

[lbendlin]

There are a couple of fallacies at play here.  First off - months do not have the same number of days.  Then you are not considering weekends or holidays, as well as workdays shifting across time. And finally you are ignoring business hour ranges.

What insights and actions are you trying to get from this report?

[Creative_tree88]

@lbendlin My business is a call centre, we 'see / talk to' clients on a 24 / 7 basis, even bank holidays, Xmas etc.  It's a round the clock business.

We want to look at our productivity per hour in terms of no. clients seen / spoken to, as new technology needs to be evaluated in business cases to determine whether they have helped in our efficiency or not.  Hence looking at average per hour.

As already detailed, I know how many clients, on average, we see / talk to per day.  That's a done deal with the formula 'A Test' in my sample data.

I accept each month has a different number of days, but when looking at Apr-14 numbers, you can see the total at the bottom is NOT the average per hour, over the course of the month.  The average in this respect should be 1.28 clients per hour for Apr-14.  

I need a formula, to be able to calculate this - hence my question and sample data.  It seems it's a tricky one!  Once I have a formula I can apply it to the 10 years of data we have, and understand how, at various points in history when we have invested in new technology, or had a recruitment drive, the productivity / efficiency has increased (or not).

Thanks for your help. 

[lbendlin]

Not sure where the 1.28 for Apr 14 comes from. I get different values.

[Creative_tree88]

@lbendlin Looks like it's 'counting' the blank cells and then dividing by all the days.  That makes sense but I need to only count the days where there is a number, to find the true average based on those only and not the blank cells.

Is there a way to do this?  Back in 2014/15 there would have been quite a few days where clients were not seen / talked to on the phone, as beginning of business.  However, later years all days will be covered with a digit.

However, need to adjust for calculating those cells with blank in them - sorry to be a pain, but I think it's close.  I get your calculations, just need to take blank cells into account.

[lbendlin]

My business is a call centre, we 'see / talk to' clients on a 24 / 7 basis, even bank holidays, Xmas etc.  It's a round the clock business.

Is it , or is it not?

[Creative_tree88]

@lbendlin It is - don't forget, this is sample data, so it is not going to be 100% correct to my business model I'm using.  As I said, there WILL be blank days at the beginning when we were starting up.  

Just wanting to find a way to calculate this - if you can help, great.  It looks like this has stumped most of the community.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Creative_tree88]

@lbendlin To do that would mean providing 10 years worth of data!?  Surely my issue was self explanatory - I need average numbers of clients seen, per hour - but averaged out on a monthly basis.  Some days will have blanks (certainly around 2014/15) as per my sample data, but the principle should be consistent i.e. only count those days with data, to form accurate average, regardless of whether that date has a full set of numbers or partial.  This IS representative of the data.

My expected outcome has been detailed earlier in the original message...??

[foodd]

@Creative_tree88 , @lbendlin's reply is of good nature and tempered with much value and experience.   Would you please consider trimming a resonable sample data set that fully covers your issue, and support that with expected outcome based on the sample data provided.   If a yearly view is of interest, slice off a rolling year worth as your bucket.

[Creative_tree88]

@foodd and @lbendlin  I'll sort some data etc out for you. Thanks for your persistence.

[Anonymous]

Turn on Date/Time intelligence and let Power BI create a 'date dimension' with Year and Month columns
Go to File menu/select Options and go to Current file/Data Load