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: