Forum Discussion
tjowen
7 years agoRegular Visitor
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.
- Anonymous7 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
- AnonymousNot 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