Rolling average of a sum

[davmor]

I need to calculate the 3 day rolling average of a sum. Couple of items which are tricky:

1) I cannot use the built in rolling average function as I need to use the last 3 available dates which might not be the last three calendar date (weekend, holidays, etc.)

2) I first need to sum the data for a given date and then take the average across dates

This is an example of my data set

Component	Date	Amount
Component A	Date 1	79.86
Component B	Date 1	93.14
Component C	Date 1	24.89
Component A	Date 2	64.29
Component B	Date 2	99.21
Component C	Date 2	70.75
Component A	Date 3	82.23
Component B	Date 3	25.86
Component C	Date 3	83.60
Component A	Date 4	73.14
Component B	Date 4	80.40
Component C	Date 4	28.74
Component A	Date 5	47.62
Component B	Date 5	31.29
Component C	Date 5	67.56
Component A	Date 6	79.08
Component B	Date 6	97.87
Component C	Date 6	91.66
Component A	Date 7	38.20
Component B	Date 7	37.08
Component C	Date 7	6.24
Component A	Date 8	23.28
Component B	Date 8	14.21
Component C	Date 8	42.81
Component A	Date 9	34.29
Component B	Date 9	55.87
Component C	Date 9	20.05
Component A	Date 10	25.43
Component B	Date 10	96.04
Component C	Date 10	96.95

This is what my output should look like (I actually do not need the "Sum of Amount" column, but I thought it might be easier to show the calculation).

Date	Sum of Amount	Rolling Average
Date 1	197.89
Date 2	234.24
Date 3	191.70	207.9417
Date 4	182.29	202.7412
Date 5	146.46	173.4826
Date 6	268.61	199.1193
Date 7	81.52	165.5311
Date 8	80.30	143.4749
Date 9	110.21	90.6753
Date 10	218.41	136.3054

Thanks in advance for any help I get.

[davmor]

Rather than a column I probably should have said a measure.

[MarkLaf · Accepted solution]

Can't speak to how efficient/smart this is but it seems to work for the test data provided (maybe a better solution would be to use a ranked date list?):

Measure = 
AVERAGEX(
    VALUES(Data[Date]),
    VAR AvgBase = 3
    VAR DateIter = [Date] 
    VAR CurDateSubset = FILTER(DISTINCT(ALL(Data[Date])),[Date]<=DateIter)
    VAR Last3Dates = TOPN(AvgBase,CurDateSubset,[Date],DESC)
    VAR AvgBaseExists = IF(COUNTROWS(Last3Dates)=AvgBase,AvgBase,BLANK()) 
    RETURN 
    CALCULATE(DIVIDE(SUM(Data[Amount]),AvgBaseExists),Last3Dates)
)

Note that this is set up to easily change the last '3' dates to some other number (change AvgBase = 3 line).

The AVERAGEX will provide an average at summary levels, if you want a sum then do SUMX (or MAXX for the max, etc.). Note that summary level is averaging/adding/etc. the calculated values, not the real average/sum of the underlying data.

**Edit: same concept except avoiding an iterator X function (so, no summary values / will only work if Date column is in visual):

Measure 2 = 
VAR AvgBase = 3
VAR DateIter = SELECTEDVALUE(Data[Date])
VAR CurDateSubset = FILTER(DISTINCT(ALL(Data[Date])),[Date]<=DateIter)
VAR Last3Dates = TOPN(3,CurDateSubset,[Date],DESC)
VAR AvgBaseExists = IF(COUNTROWS(Last3Dates)=AvgBase,AvgBase,BLANK())
RETURN 
CALCULATE(DIVIDE(SUM(Data[Amount]),AvgBaseExists),Last3Dates)

[davmor]

Thanks a lot. I had seen many replies in other theads, but none seemed to work as expected. This one (I tried the second one) works perfectly and seems to be very quick.