Calculating Average Hours Worked/Week by Week, Month, Year

[Anonymous]

Hey everyone, I believe this should be a fairly straight forward question, so let's give it a shot! I have a very simple database that tracks daily employee hours worked (see below for a small sample of how it is laid out). I also have a calendar table. I am looking to calculate the Average Hours Worked per each employee, across the company. The calculation should be simple. I would Sum Hours Worked, Divided by Count of Employees,  Divide by Count of Total Weeks. The problem is, I'm not sure how to account for partial weeks? For example, if I wanted to show our company's Average Hours Worked when I filter my report to January 2019 the count of 'Total Weeks' becomes 5 as there is technically a partial week in the month. This understates our Average Hours Worked. It would be easy if I knew that the user was going to consistenly be looking at the report by Month, or Quarter, or Yearly, then I could just hard code 4, 12, & 52 respectively, but I need this to be dynamic. 

[v-juanli-msft]

Hi @Anonymous 

Create a date table connected to main data table,

date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2),"weekday",WEEKDAY([Date],2))

Then create measures in main data table

total week = IF(WEEKDAY(LASTDATE('date'[Date]),2)<>7,COUNTX(VALUES('date'[weeknum]),'date'[weeknum])-1,COUNTX(VALUES('date'[weeknum]),'date'[weeknum]))

average = SUM(Sheet3[hours])/DISTINCTCOUNT(Sheet3[employee])/[total week]

(assume start of a week is Monday, end is Sunday)

Best Regards
Maggie

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

[Anonymous]

Thank you, this helped so much! The total week formula works perfectly when I filter by month or quarter from my calendar table, however it doesn't seem to filter by year? For example, when I select 2019 I'd like it to total the number of full weeks completed YTD. Selecting 2018 would display 52 weeks; the formula currently displays 51 weeks for some reason? Selecting 2019 would show the count up to the date my data is through. 

Also, when I select "Jan, Feb, Mar" of 2019, shouldn't the result come out to 12?

Thank you,

[v-juanli-msft]

Hi @Anonymous 

Per your requirements, my final solution is

Create measures in main data table(not date table)

maxdate = CALCULATE(MAX(Sheet3[date]),ALLSELECTED('date'))

days_per week = CALCULATE(SUM('date'[weekday]),FILTER(ALLSELECTED('date'),'date'[weeknum]=MAX('date'[weeknum])))

total week = COUNTX(FILTER(VALUES('date'[weeknum]),[days_per week]=28),'date'[weeknum])

year or year-month = IF(ISFILTERED('date'[year]),IF(ISFILTERED('date'[month]),2,1))

final total week =
IF (
    [year or year-month] = 1
        && SELECTEDVALUE ( 'date'[year] ) = YEAR ( TODAY () ),
    CALCULATE ( [total week], FILTER ( Sheet3, Sheet3[date] <= [maxdate] ) ),
    [total week]
)

average = SUM(Sheet3[hours])/DISTINCTCOUNT(Sheet3[employee])/[final total week]

In my test, my data is to 2019/2/13 for 2019, so the total week is 5 for this period.

Best Regards
Maggie

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

[Anonymous]

Hi Maggie, thank you for your response. It looks like your solution works for company hours, but how would I have it display Employee hours? I checked on the PBIX file you were kind enough to attach, and this is the result:

[Anonymous]

Just wanted to give this a bump, so close!

[v-juanli-msft]

Hi @Anonymous 

On my side,

Please show me your formula about the "total week" measure.

I modify my formula "total week" as below, so i can get 12 as you show

days_per week = CALCULATE(SUM('date'[weekday]),FILTER(ALLSELECTED('date'),'date'[weeknum]=MAX('date'[weeknum])))

total week = COUNTX(FILTER(VALUES('date'[weeknum]),[days_per week]=28),'date'[weeknum])

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

when you select "january", the number of total weeks is 5, but you need the "Average Hours Worked/Week" =

Sum Hours Worked(1/1~1/31), Divided by Count of Employees,  Divide by Count of Total Weeks(4),

Right? 

Or the number of weeks for "january" =4+4/7

Best Regards

Maggie

[Anonymous]

Hi Maggie,

---

@v-juanli-msft wrote:

when you select "january", the number of total weeks is 5, but you need the "Average Hours Worked/Week" =

Sum Hours Worked(1/1~1/31), Divided by Count of Employees,  Divide by Count of Total Weeks(4),

Right? 

---

Yes, that is correct. I know this is not strictly a PowerBI question, and more of a general analytical one, but I am just having difficulty with understanding how to do this kind of calculation. 

Thank you,

Mike