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.

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