Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
tjowen
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:

 

image.png

 

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:

 

image.png

 

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

View solution in original post

1 REPLY 1
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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.