Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

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...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Just wanted to give this a bump, so close!

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      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's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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: