cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam10
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.

DateCases
1/1/202065
1/2/202086
1/3/202035
1/4/202085
1/5/202042
1/6/202068
1/7/202098
1/8/202086
1/9/2020114
1/10/202064
1/11/202095
1/12/202092
1/13/202057

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])
 Cases.png

 

1 ACCEPTED SOLUTION
v-caitlyn-mstf
Community Support
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:

Sample.png

 

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.

View solution in original post

4 REPLIES 4
Sam10
Frequent Visitor

Works great! Thank you for helping. 

v-caitlyn-mstf
Community Support
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:

Sample.png

 

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.

View solution in original post

amitchandak
Super User IV
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!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

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.