Rolling average over time with multiple values per date

Question

I'm trying to calculate a rolling average for each row of a table based on values present in this table based on a sliding time window looking ahead and back a certain amount of days.

Given the following table:

myTable
+------------+-------+
| Date       | Value |
+------------+-------+
| 31/05/2020 | 5     |
+------------+-------+
| 31/05/2020 | 10    |
+------------+-------+
| 01/06/2020 | 50    |
+------------+-------+
| 01/08/2020 | 50    |
+------------+-------+

and the measure

myMeasure =
VAR LookAheadAndBehindInDays = 28
RETURN
    AVERAGEX (
        DATESINPERIOD (
            myTable[Date],
            DATEADD ( LASTDATE ( myTable[Date] ), LookAheadAndBehindInDays, DAY ),
            -2 * LookAheadAndBehindInDays,
            DAY
        ),
        myTable[Value]
    )

I checked that the DATESINPERIOD returns effectively the right dates. My problem lies in the calculation of the average.

Instead of calculating the average of all values directly (expected result)

+------------+-------+---------------------------+
| Date       | Value | myMeasure                 |
+------------+-------+---------------------------+
| 31/05/2020 | 5     | (5 + 10 + 50) / 3 = 21.66 |
+------------+-------+---------------------------+
| 31/05/2020 | 10    | (5 + 10 + 50) / 3 = 21.66 |
+------------+-------+---------------------------+
| 01/06/2020 | 50    | (5 + 10 + 50) / 3 = 21.66 |
+------------+-------+---------------------------+
| 01/08/2020 | 27    | 27 / 1 = 27               |
+------------+-------+---------------------------+

It first calculates the average of each date, and then the average of those values:

+------------+-------+--------------------+------------------------+
| Date       | Value | Avg. by Date       | myMeasure              |
+------------+-------+--------------------+------------------------+
| 31/05/2020 | 5     | (5 + 10) / 2 = 7.5 | (7.5 + 50) / 3 = 28.75 |
+------------+-------+--------------------+------------------------+
| 31/05/2020 | 10    | (5 + 10) / 2 = 7.5 | (7.5 + 50) / 3 = 28.75 |
+------------+-------+--------------------+------------------------+
| 01/06/2020 | 50    | 50 / 1 = 50        | (7.5 + 50) / 3 = 28.75 |
+------------+-------+--------------------+------------------------+
| 01/08/2020 | 27    | 27 / 1 = 27        | 27 / 1 = 27            |
+------------+-------+--------------------+------------------------+

I found out about this behavior by using this measure:

myMeasure DEBUG =
VAR LookAheadAndBehindInDays = 28
VAR vTable =
    DATESINPERIOD (
        myTable[Date],
        DATEADD ( LASTDATE ( myTable[Date] ), LookAheadAndBehindInDays , DAY ),
        -2 * LookAheadAndBehindInDays,
        DAY
    )
RETURN
    FIRSTDATE ( vTable ) & " - " & LASTDATE ( vTable ) & UNICHAR(10) 
        & " - Row Count: " & COUNTROWS ( vTable )  & UNICHAR(10) 
        & " - Avg: " & AVERAGEX(vTable, myTable[Value]) & UNICHAR(10) 
        & " - Dates: " & CONCATENATEX ( vTable, myTable[Date], "," ) & UNICHAR(10) 
        & " - Values: " & CONCATENATEX ( vTable, myTable[Value], "," )

This returns for rows with the date '31/05/2020' and '31/05/2020' the following value:

31/05/2020 - 01/06/2020
Row Count: 2
Avg: 28.75
Dates: 31/05/2020,01/06/2020
Values: 7.5,50

Most notable are the Row Count 2, which I would expect to be 3 and the values 5,10 and 50 (as reflected above in the tables)

So my question is, how can in calculate the rolling average over time by weighting each value equally, instead of weighting each day equally.

Answer 1

I'm not sure I completely understood the problem, but to me you just need a standard AVERAGE and not the AVERAGEX iterator.

I've changed the formula a bit and didn't use DATESINPERIOD, this one achieves the same result and (to me) is more clear and readable

Avg =
VAR DaysInterval = 28
RETURN
    CALCULATE (
        AVERAGE ( myTable[Value] ),
        DATESBETWEEN (
            myTable[Date],
            MAX ( myTable[Date] ) - DaysInterval, --from
            MAX ( myTable[Date] ) + DaysInterval  --to
        )
    )

here is the result (based on the sample dataset)

Answer 2

What you are looking for is the calculated average from the days -/+28:

myMeasure = 
VAR LookAheadAndBehindInDays = 28
var curDAte = rolling[ Date]
return CALCULATE(AVERAGE(rolling[Value]), 
    FILTER(rolling,  
        rolling[ Date] +LookAheadAndBehindInDays >= curDAte && 
        rolling[ Date] -LookAheadAndBehindInDays <= curDAte))

as you can see I am using the filter to get the rows falling in the date range and calculate the average over those.