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 

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: