Bollinger Bands for every column (time series)

[Anonymous]

Hello colleagues,

I am facing an hard time to create the 2 Bollinger Bands (wikipedia):

Where the Upper bollinger band is : rolling average of previous 20 days + 2 * (20 days rolling standard deviation)

&

Lower band: average of previous 20 days - 2 * (20 days rolling standard deviation)

The data is a time series of 3 different contracts (MATURITY is the name of each contract):

MATURITY	OBS_DATE (MM/DD/YYYY)	PRICE
06M	01/01/2019	8
06M	01/02/2019	8.5
06M	01/03/2019	7
08M	01/01/2019	2.5
08M	01/02/2019	3
08M	01/03/2019	2.5
01M	01/01/2019	1
01M	01/02/2019	3
01M	01/03/2019	9

*TABLE name: ''L1''

that I unpivoted on maturity to have the time series as:

OBS_DATE (MM/DD/YYYY)	01M	06M	08M
01/01/2019	1	8	2.5
01/02/2019	3	8.5	3
01/03/2019	9	7	2.5

*TABLE name: ''L1_Unpivoted''

%% for sake of example, Ill end up with (taking 2 just days rolling average/st dev):

OBS_DATE	01M_BB_Up	01M_BB_Down	06M_BB_Up	etc...etc
1/1/2019	null	null	null
1/2/2019	4	0	8.75
1/3/2019	12	0	9.25

Now I would like to create the bollinger bands, so that at the end I get the visualization of:

1) the time series

2) bands

---> but the best would be to have them 'toggable' so I can decide what to visualize.

Very gratefull to for your help, please let me know!

 Best,

Luca.

[prirao0004]

i want to find out std dev for 20 days for bollinger bands, i'm getting 0.00 for all the values. Please helpwith this. 

stddev =

var c=STDEVX.P(TOPN(20,filter(all('stock'[Index]),'stock'[index]<=MAX('stock'[Index]))), (SELECTEDVALUE('stock'[Close])))

return c

another formula i used was:
stddev=

var PeriodEnd = LASTDATE('stock'[Date])

var PeriodStart= FIRSTDATE( DATESINPERIOD('stock'[Date], PeriodEnd, -20, DAY))

var stddev = CALCULATE(stdev.p('stock'[Close]),DATESBETWEEN ( 'stock'[Date], PeriodStart, PeriodEnd ))

return stddev

but i'm getting 0 as the answer.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can try to create measures to get average of previous 20 days like DAX below.

Period End = LASTDATE(L1[Date])
 
Period Start= FIRSTDATE( DATESINPERIOD(L1[Date], [Period End], -20, DAY))
 
Rolling 20 Day Avg = CALCULATE(AVERAGE(L1[PRICE]),DATESBETWEEN ( L1[Date], [Period Start], [Period End] ))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.