Forum Discussion

gunicotra's avatar
gunicotra
Helper II
4 years ago
Solved

Moving average for Bollinger bands

Hi to all,

I have a financial data on several Stocks I wish I can anilyze them with a Bollinger bands approach. Thus I started from a 20 days moving average with this formula:


MovingAverage20d =
Var PeriodEnd = LASTDATE('History-TopStocks'[Date])
Var PeriodStart= FIRSTDATE( DATESINPERIOD('History-TopStocks'[Date], PeriodEnd, -20, DAY))
Return
CALCULATE(AVERAGE('History-TopStocks'[Adj Close]),
DATESBETWEEN('History-TopStocks'[Date], PeriodStart, PeriodEnd))

 


However it does not sum at all (i.e.: each MovingAverage20d item is the same as AdjClose), neither makes the average on them! I attach a screeenshot for further details.

Any ideas?

Thanks

Need Help
  • gunicotra's avatar
    gunicotra
    4 years ago

    by trying and trying, I got a solution that wonder whether it appears good also for you.


    With this code, I may have found the Simple Moving Average of 20 days also taking into consideration the fact that I have several stocks in my table. 


    SMA =
    CALCULATE(sum('History-TopStocks'[Adj Close]),
         DATESINPERIOD('History-TopStocks'[Date],
                    LASTDATE('History-TopStocks'[Date]),-20, DAY),
    ALLEXCEPT('History-TopStocks','History-TopStocks'[Symbol]))
    /
    CALCULATE(DISTINCTCOUNT('History-TopStocks'[Date]),
           DATESINPERIOD('History-TopStocks'[Date],
                  LASTDATE('History-TopStocks'[Date]),-20, DAY),
    ALLEXCEPT('History-TopStocks','History-TopStocks'[Symbol]))

    What do you think?

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    gunicotra , Seem like you need a column, try like

     

    New column =

    var _dt = 'History-TopStocks'[Date]

    CALCULATE(AVERAGEX(filter( 'History-TopStocks', [Date] >= _dt-20 && [Date] <= _dt) 'History-TopStocks'[Adj Close]))

     

    if you need a measure, with help from date table

     

    Rolling 20 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-20,DAY))

    • gunicotra's avatar
      gunicotra
      Helper II
      4 years ago

      hi amitchandak!
      I tried either with your:

       
      CALCULATE(AVERAGEX(filter( 'History-TopStocks', [Date] >= _dt-20 && [Date] <= _dt), 'History-TopStocks'[Adj Close]))
       
      or with:

      CALCULATE(AVERAGEX(filter( 'History-TopStocks', [Date] >= _dt-20 && [Date] <= _dt), sum('History-TopStocks'[Adj Close])))
       
      however in both methods I had the same result: AdjClose = MovingAverage20d (see img below).


       

       
      • gunicotra's avatar
        gunicotra
        Helper II
        4 years ago

        Moreover, my table has got several Stocks in it, therefore the moving average calculations should start from the beginning as the stock changes.... difficulty inside the difficulty (at least for me!!)