Forum Discussion

wlf0032's avatar
wlf0032
Frequent Visitor
4 years ago
Solved

How to create Rolling Average with +/-St. Dev. Band

Hi,

I have a time series column (e.g. date with stock index price). I want to show this stock index as line chart, with 365-day moving average, and +/- 1 std. dev. I created 3 measures on this index:  the moving average (MA); the MA+1 std. dev; and MA-1 std. dev. So the chart should like this:

 

Besides, I want the date-axis respond to a date slicer. But when I change the dates slicer, the chart becomes the following. The date-axis didn't respond, only the stock index partially disappeared, and the 3 measures also behaving wierdly.

(P.S. the measure's value for each day shouldn't change when the date slicer changes)

 

So, how do I modify my formula to make this work? Here's the formula for the MA+1 std. dev. measure. 

 

Many thanks! Much appreciate you help!

 

 

 

StockIndex_1YMA+sd = 
VAR __LAST_DATE = LASTDATE('Data'[Dates].[Date])
RETURN
	AVERAGEX(
		DATESBETWEEN(
			'Data'[Dates].[Date],
			DATEADD(__LAST_DATE, -365, DAY),
			DATEADD(__LAST_DATE, 365, DAY)
			),
		CALCULATE(AVERAGE('Data'[StockIndex]))
       )
    + CALCULATE(STDEVX.S('Data','Data'[StockIndex]),ALL())

 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi wlf0032 

    Do you want to keep the visual in red box and show others in blank?

    I think you can add a If function in your measure.

    New = 
If (Date[Dates]>= MIN(Date[Dates])&&Date[Dates]<=MAX(Date[Dates]),[StockIndex_1YMA+sd],blank())

    Or you can try to create an unrelated date table to build the slicer. Then create New measure based on unrelated date table.

     

    Best Regards,
    Rico Zhou

     

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

5 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    wlf0032 

    I created a Power BI file using MSFT close value, please check if this fits your requirements. I didn't quite understand +1 and -1 here. 

    MA 365 = 
CALCULATE(
    AVERAGE(MSFT[Close]),
    DATESINPERIOD( Dates[Date] , MAX(Dates[Date]) , -365 , DAY )
)

    Std + 1 = 
[MA 365] + 
STDEVX.S(
    DATESINPERIOD( Dates[Date] , MAX(Dates[Date]) , -365 , DAY ),
    [Close Value]
)
    Std - 1 = 
[MA 365] - 
STDEVX.S(
    DATESINPERIOD( Dates[Date] , MAX(Dates[Date]) , -365 , DAY ),
    [Close Value]
)

    Check the attachment below my signature

    • wlf0032's avatar
      wlf0032
      Frequent Visitor
      4 years ago

      Really appreciate your solution! I tried your way, but this seems doesn't solve my problem.

      1. the std dev measure shouldn't be a moving measure. It should be static, i.e. std dev of all available data.

      2. this won't help with the date slicer issue. when I filter the date slicer, this std dev still shows on the chart for those dates being filtered out.

       

      Thanks anyway!

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        wlf0032 

        Got it, you can calculate the STDEV as follows. I did not understand what you meant by  "and +/- 1 std. dev. "

        Std + 1 = [MA 365] + STDEVX.S( ALL( Dates[Date] ) ,  [Close Value] )


Std - 1  = [MA 365] + STDEVX.S( ALL( Dates[Date] ) ,  [Close Value] )

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi wlf0032 

    Do you want to keep the visual in red box and show others in blank?

    I think you can add a If function in your measure.

    New = 
If (Date[Dates]>= MIN(Date[Dates])&&Date[Dates]<=MAX(Date[Dates]),[StockIndex_1YMA+sd],blank())

    Or you can try to create an unrelated date table to build the slicer. Then create New measure based on unrelated date table.

     

    Best Regards,
    Rico Zhou

     

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

    • wlf0032's avatar
      wlf0032
      Frequent Visitor
      4 years ago

      thanks! IF functions works! but i did it the following way (1Y MA is blank when dates are not selected):

       

      IF(ISBLANK([Index 1YMA]),"",[Index 1YMA] - CALCULATE(STDEVX.S('Data','Data'[Index]),ALLSELECTED()))