Average not Working

[andre_Work]

Hi everyone, i'm having troubles on calculating the average 

the table contains a number value let call it "Total", and the date it's entered

i would like to get on a virtual colum the average based on the month it's entered

i'm using this formula:
AVERAGE(
 SELECT(GUILDWAR[TOTAL],
  AND([DATE] > EOMONTH([DATE], -1) , [DATE] <= EOMONTH([DATE], 0) )))

and this is the data i'm referring to:

DATE	FIRST	SECOND	TOTAL	NOTE	MONTHLY AVG
31/05/2022	1		1	Normal	3,69
01/06/2022	3	3	6	Normal	4,33
02/06/2022	3	3	6	Normal	4,33
03/06/2022	3	3	6	Normal	4,33
04/06/2022	3	0	3	Normal	4,33
05/06/2022	3	3	6	Normal	4,33
06/06/2022	3	1	4	Normal	4,33
07/06/2022	1		1	Normal	4,33
08/06/2022	3	1	4	Normal	4,33
09/06/2022	2	1	3	Normal	HERE

on 09/06 entry it returns 3,3 but it should return 4,33

can someone explain me where i'm wrong?

[Marc_Dillon · Accepted solution]

Your usage of [Date] is ambiguous. The system doesn't understand that one instance is intended to look at the current record, and the other instance to look at the searched record. You need to use [_THISROW] to differentiate them.

https://help.appsheet.com/en/articles/2357314-select

[andre_Work]

thanks, looks like it's working!
the only issue is that it gives out the average based only on previous entries not counting the one being inserted 
fine enough for my use, i'm just writing this down hoping it could help somebody else too !