Forum Discussion
Calculating Average Hours Worked/Week by Week, Month, Year
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,
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.
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: