Search for max in data related to a date

[Danny67]

We're looking for a formula or method to search the maximum in a time range in a table which contains 2 coloms.

First colom contains dates (no relation between dates, random)

Second colom contains numbers (mesures) related to the specific date of colom 1.

We want to search for each row (date and mesure) the maximum in all the mesures in the periode a year before the date and 4 months after the date.

[#2]

Hello @Danny67,

For Max last year, that could be:

=MAXIFS([Mesure],[Date],"<="&[@Date],[Date],">="&DATE(YEAR([@Date])-1,MONTH([@Date]),DAY([@Date])))

For Max 4 months, that could be: 

=MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date]),MONTH([@Date])+4,DAY([@Date])))

For Max after 1 year,

 =MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date])+1,MONTH([@Date]),DAY([@Date])))

Please review for correctness. 

[#3]

@Danny67 

In C2:

=MAXIFS([Mesure],[Date],">="&EDATE([@Date],-12),[Date],"<="&[@Date])

In D2:

=MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&EDATE([@Date],4))

In E2:

=MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&EDATE([@Date],12))

[#4]

@PReagan 

Hey,

If I do that I become a zero in every cell.

[#5]

@PReagan 

Have found the problem. Now I would make one change.

If i would calculate this only within the same parcel, and for each parcel seperate. How do i make this supplemantary condition?

[#6]

@Danny67 

Include the criteria [@Parcel] to the criteria_range [Parcel]:

For Max last year, that could be:

=MAXIFS([Mesure],[Parcel],[@Parcel],[Date],"<="&[@Date],[Date],">="&DATE(YEAR([@Date])-1,MONTH([@Date]),DAY([@Date])))

For Max 4 months, that could be: 

=MAXIFS([Mesure],[Parcel],[@Parcel],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date]),MONTH([@Date])+4,DAY([@Date])))

For Max after 1 year,

 =MAXIFS([Mesure],[Parcel],[@Parcel],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date])+1,MONTH([@Date]),DAY([@Date])))