cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling 7 Day Average of a Rolling 7 Day Sum DAX

Hello,
Im calculating the rolling 7 day sum of a field like so:

7 Day Rolling Sum ED Eval =
CALCULATE (
SUM ( 'Table'[Metric] ),
FILTER (
ALLSELECTED ( 'Table'[Date] ),
'Table'[Date]
<= MAX ( 'Table'[Metric] )
&& 'Table'[Date]
>= MAX ( 'Table'[Date] ) - 6
)
)
This gives me the rolling sum of metric like so

 Date Metric Rolling 7 day Sum 4/1/2020 1 1 4/2/2020 1 2 4/3/2020 1 3 4/4/2020 1 4 4/5/2020 1 5 4/6/2020 1 6 4/7/2020 1 7 4/8/2020 1 7

I would like another column like so:

 Date Metric Rolling 7 day Sum Rolling 7 day sum avg 4/1/2020 1 1 1 4/2/2020 1 2 1.5 4/3/2020 1 3 2 4/4/2020 1 4 2.5 4/5/2020 1 5 3 4/6/2020 1 6 3.5 4/7/2020 1 7 4 4/8/2020 1 7 4.85

I cannot setup the rolling sum measure as a calculated column or something in the dataset as the rolling period needs to be dynamic.
Any help would be appreciated!

1 ACCEPTED SOLUTION
Super User IV

@hwr7dd , for a week, I usually add these in date tables

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measure  like

Last 7 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-7 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Proud to be a Super User!

5 REPLIES 5
Community Support

Hi, @hwr7dd

You could create a measure by the  following:

``````avg =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
[Date] <= MAX ( 'Table'[Date] )
&& [Date]
>= MAX ( 'Table'[Date] ) - 6),
[7 Day Rolling Sum ED Eval])
``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III

Hi,

In the third column of the second table, you want to create an average from the first of the month to the date of the current row.  Is my understanding correct?  Also, what is the 1 appearing at the end of the 2020?  Furthermore, i do not see a Calendar Table.  You must have a Calendar Table there.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User IV

@hwr7dd , Try a measure like this with help from the date table

Rolling 7 = CALCULATE(SUM ( 'Table'[Metric] ),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))

Proud to be a Super User!

Frequent Visitor

@amitchandak That gives me daily. Im trying to get Week over Week Rolling Average

Super User IV

@hwr7dd , for a week, I usually add these in date tables

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measure  like

Last 7 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-7 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Proud to be a Super User!

Announcements