Getting a running average

[tjowen]

I have created a calculated table that contains two fields:  a date (no time), and a numerical value for each date.

It looks something like this:

I am trying to get a running average of the previous 30 days for each date.  I have tried the following columns:

DIVIDE(
CALCULATE(
SUM(DaySum[Cargo Tonnes]),
DATESBETWEEN(
'DaySum'[Date],
DATEADD('DaySum'[Date],-30,DAY),
'DaySum'[Date]
)
),30)

AND

DIVIDE(
CALCULATE(
SUM(DaySum[Cargo Tonnes]),
DATESINPERIOD (
        'DaySum'[Date],
        DaySum[Date],-30,DAY
)),30)

AND

DIVIDE(
CALCULATE(
SUM(DaySum[Cargo Tonnes]),
DATESBETWEEN(
DaySum[Date],
FIRSTDATE(DATEADD(DaySum[Date],-30,DAY)),
LASTDATE(DaySum[Date])
)
),30)

But the result is always the [Cargo Tonnes] field divided by 30.  It never seems to SUM the preceeding 30 days, then divide that sum by 30, thus:

Can anyone suggest where I might be going wrong?

Thanks!

Tom.

[Anonymous · Accepted solution]

Hey Tom,

By including a filter, ALL(DaySum), you will achieve the results you are looking for. See below for an example.

30 Day Average = 
DIVIDE(
    CALCULATE(
        SUM(DaySum[Cargo Tonnes]), 
        ALL(DaySum),
        DATESBETWEEN(DaySum[Date], LASTDATE(DaySum[Date])-30, LASTDATE(DaySum[Date]))
    )
,30)

Kind regards,

Alex