Forum Discussion

tjowen's avatar
tjowen
Regular Visitor
7 years ago
Solved

Getting a running average

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's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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