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

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