Rolling 7 Day Average of a Rolling 7 Day Sum DAX

[hwr7dd]

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!

[v-yalanwu-msft]

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.  

[Ashish_Mathur]

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.

[amitchandak]

@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))

[Joel_Clark]

Beautiful clean DAX solution - pure as it was intended!

[hwr7dd]

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

[amitchandak · Accepted solution]

@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])))