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
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