Moving average for Bollinger bands

[gunicotra]

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

[amitchandak]

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

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]

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

[gunicotra · Accepted solution]

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?