Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Bollinger Bands for every column (time series)

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

MATURITYOBS_DATE (MM/DD/YYYY)PRICE
06M01/01/20198
06M     01/02/20198.5
06M        01/03/20197
08M01/01/20192.5
08M   01/02/20193
08M      01/03/20192.5
01M01/01/20191
01M    01/02/20193
01M      01/03/20199

*TABLE name: ''L1''

 

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

 

OBS_DATE (MM/DD/YYYY)        01M        06M   08M
01/01/2019182.5
01/02/201938.53
01/03/2019972.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_Up01M_BB_Down06M_BB_Upetc...etc
1/1/2019nullnullnull 
1/2/2019408.75 
1/3/20191209.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.

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
prirao0004
New Member

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

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.