cancel
Showing results for
Search instead for
Did you mean: Frequent Visitor

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

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]) 1 ACCEPTED SOLUTION  Community Support

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 @amitchandakyou 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：

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.

4 REPLIES 4 Frequent Visitor

Works great! Thank you for helping.  Community Support

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 @amitchandakyou 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：

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.  Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User! Frequent Visitor

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 %])  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (17,247)