average with only syntax

[jalpashukla]

Hello everyone,

I have a data in which I'm showing max date only but adding two additional calculated columns in table. These columns includes average value of max date and previous date of max date.  

Table:

ID	Date	Value
800123	2/17/2021	4
800123	2/17/2021	3
800123	2/17/2021	2
800123	2/17/2021	4
800123	1/14/2021	5
800123	1/14/2021	2
800123	1/14/2021	3
800123	1/5/2021	4
800123	1/3/2021	2

Expected Result:

ID	Date	Value	Avg of maxdate(2/17/2021)	Avg of P_date(1/14/2021)
800123	2/17/2021	4	3.25	4
800123	2/17/2021	3	3.25	4
800123	2/17/2021	2	3.25	4
800123	2/17/2021	4	3.25	4

Issues facing while trying few things:

1) Not getting the average value of P_date in front of the max date 

2) Not able to add average in set analysis with only condition

I have tried below things:

• only({<P_Date = Date >}value)
• avg({<P_Date= Date>}at_feedback_rating_id_1)
• avg( {$<Date = {$(#=Only(P_Date) = Date)}>} value)