Day(Month) gives you the day number in the month, so 22, or 31, or ...
day( '1971-10-30' ) returns 30.
When you subtract the days, you don't automatically receive the right number of days, since there could be a month transition or a period of several months.
What you need it to subtract the day numbers from eachother is the actual number that represents the date. Use Num(Date) to get that number.
Num(Day(ENDMONTH)) - Num(Day(Received))
I tried but it didn't work
I think there should also be something wrong about the logic because I'm receiving really weird numbers as output
BTW, in the end I managed to have the correct result but really I can't tell why the formula is working.
I used this to take account of substraction when the dates are from different months:
MONTHEND(Received,MONTH-MONTH(Received)) - Received
What is strange is that:
1- I used a no distinct AVG formula
2-I noticed that the output of the nodistinct formula was adding 1 to the correct result, so I used :
(AVG( nodistinct expression) -1).
Honestly, I haven't understood why it is working, there's something I'm missing about logic here
Guess I'll have to make more tests to see if it works even with a different dataset