Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |