Average Hours over last year - multiple job rows

[Mark Hay]

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 Id	Gross Hours	Effective Date	end
1	0	04/04/2022	03/04/2023
2	936	02/08/2022	31/03/2023
2	936	01/04/2023	03/04/2023
3	728	24/07/2021	30/09/2021
3	884	01/10/2021	31/12/2021
3	884	01/01/2022	10/09/2022
3	312	11/09/2022	30/09/2022
3	312	01/10/2022	21/11/2022
3	312	22/11/2022	31/03/2023
3	312	01/04/2023	03/04/2023
4	2080	15/11/2021	30/09/2022
4	2080	01/10/2022	30/11/2022
4	2080	01/12/2022	03/04/2023

[#2]

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

[#3]

@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

[#4]

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,