cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular 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],
'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],
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.

1 ACCEPTED SOLUTION
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
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.