Calculating the average (measure or column) of the % change of 7 days moving average (measure)

[Sam10]

Hi all, 

I ran into a problem and will really appreicate any help. 

I have this dataset (Cases) with two columns, Date and Cases.

Date	Cases
1/1/2020	65
1/2/2020	86
1/3/2020	35
1/4/2020	85
1/5/2020	42
1/6/2020	68
1/7/2020	98
1/8/2020	86
1/9/2020	114
1/10/2020	64
1/11/2020	95
1/12/2020	92
1/13/2020	57

I was able to calculate the moving average of cases (Moving average) and day by day % change of the moving average (Moving %ch) using below DAX functions. Both results are measures. 

Moving average = CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Cases'[Date], LASTDATE('Cases'[Date])-6,7,DAY))/7

Moving %ch = VAR CurValue = CALCULATE('Cases'[Moving average], DATEADD('Cases'[Date], 0, DAY))

                         VAR PreValue = CALCULATE('Cases'[Moving average], DATEADD('Cases'[Date], -1, DAY))

                         RETURN

                         DIVIDE(CurValue-PreValue, PreValue)

Then, when I tried to calcualte the average of moving %ch (either as a measure or column) using either of below DAX functions. All I get are zeros and blanks. I really want to fix this and understand why I can't get the average of a measuere (Moving %ch). Thank you!

Avg moving %ch = AVERAGEX(SUMMARIZE('Cases', 'Cases'[Date], "Avg", [Moving %ch]), [Avg])

Avg moving %ch2 = AVERAGEX('Cases', [Moving %ch])

 

[Sam10]

Works great! Thank you for helping. 

[Anonymous · Accepted solution]

Hi,@Sam10 

According to your description, there is an error in your measure while using the time intelligence.

For time intelligence functions, we recommend that you use it with the Date table. It may solve the problem that always gives 0 and blanks.

You can do as the following steps：

1.Create a date table and create relationship between Date table and Cases table.

Date table = CALENDARAUTO()

2.Create a new measure for average in 7 days.

Average in 7 day = 
var _sum=CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date table'[Date],MAX('Date table'[Date]),-7,Day))
Return
_sum/7

3.It's a nice reply of @amitchandak，you can follow it：

Average % =
VAR _Rolling7day =
    CALCULATE (
        SUM ( 'Cases'[Cases] ),
        DATESINPERIOD ( 'Date table'[Date], MAX ( 'Date table'[Date] ), -7, DAY )
    ) / 7
VAR _Rolling7daylastDay =
    CALCULATE (
        SUM ( 'Cases'[Cases] ),
        DATESINPERIOD ( 'Date table'[Date], MAX ( 'Date table'[Date] ) - 1, -7, DAY )
    ) / 7
RETURN
    DIVIDE ( _Rolling7day - _Rolling7daylastDay, _Rolling7daylastDay )

Then you can meet your needs like this：

Here is the demo，please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EQWTNzzQldtGgeFIejO... 

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Sam10 , Not very clear, but it should be last 7 and last 7 for yesterday like given example

Rolling 7 day = CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,Day)) /7

Rolling 7 day last Day = CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date'[Date],MAX('Date'[Date])-1,-7,Day))/7

Change % = divide( [Rolling 7 day] -[Rolling 7 day last Day],[Rolling 7 day last Day])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Sam10]

Thank you for the response. Now from here, how  we calculate the average of the Change % as a column or a measure? I was not be able to do that. Below DAX functions can run without errors, but only give me 0 and blanks. 

Avg moving Change % = AVERAGEX(SUMMARIZE('Cases', 'Cases'[Date], "Avg", [Change %]), [Avg])

Avg moving Change % = AVERAGEX('Cases', [Change %])