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