cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

1 ACCEPTED SOLUTION
Helper II

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 =
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 4
Super User

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

Helper II

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

Helper II

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

Helper II

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 =
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?

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.