Average Hours over last year - multiple job rows

Copper Contributor

Hi, looking for some guidance. I need to be able to show what the average contract hours for an employee are over the last 365 days. My issue is that they will likely have multiple employment rows each with differing hours. 

I can work out the days & what portion of the gross hours they should have worded in that period but can't think how to sum these values over the last year only.


A sample of the source data is below. Any advice or guidance would be greatfully received!

Employee IdGross HoursEffective Dateend
3 Replies

@Mark Hay 


I copied your data to Excel--some of the dates needed to be re-entered to conform with Excel's date formats.....

And then used this formula to yield the results shown below.

=AVERAGE(FILTER(Table1[Gross Hours],(Table1[Employee Id]=G4)*YEAR(Table1[Effective Date])=2022))



 It would be easy to add the end date into the calculation as well (FILTER accommodates multiple criteria), but I don't really know what I'm looking at with your data.

It's hard to know what they mean because your sample data are--pardon me for saying it--odd. For example, employee ID 4 has gross hours of 2080 in each of the three rows, regardless of the dates associated. That may be accurate, but it makes it hard to test a formula to know if it's yielding useful results.


Anyway, the FILTER function does require Excel 2021 or newer. That link will take you to a good explanation of the function. 

@mathetes Thank you very much. Re the data - each line is showing a role change -so some will be hours some will be wages, some job titles etc

I assume you're saying that the solution I gave worked. I'd appreciate, if it IS finished to your satisfaction, if you clicked on the "Mark as best response" link, so as to close it down,