Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
I am currently working on building out a Historical Stock Market analysis.
Currently I am pulling in a parametered function that brings in the entire history of each of the symbols I am looking for. This is great (Obviously at some point I will need to find a more cost-efficient solution, but for now this is a good concept).
I have all the data pulled in and I am looking to calculate Simple Moving Averages
For those of you unfamiliar this is simple just (SUM(Close Price of the STock)) / Amount of days)
Example and I will show you what I am running into :
Date - Close Price
4/17/19 - $11
4/18/19 - $11
4/19/19 - $10
4/20/19 - (No Data -- Stock Market Closed)
4/21/19 - (No Data -- Stock Market Closed)
4/22/19 - $12
The 4 day moving average for 4/22 should include 4/22, 4/19, 4/18, 4/17
AND NOT include 4/20, 4/21
So:
($11 + $11 + 10 + 12) / 4 (days) = $44/4 = $11 4 day Simple Moving Average
And not:
($10 + No Data + No Data + $12)/ 4 (days) = $22/4 = $5.50 4 day Simple Moving Average
Here is my calculation for this:
Solved! Go to Solution.
Hi @chrisB13,
Please see the below.
4 Day Moving Avg =
VAR __maxDateInCurrentSelection =
MAX ( TickerList[Date] )
VAR __top4DaysWithValues =
TOPN (
4,
FILTER ( ALL ( TickerList ), TickerList[Date] <= __maxDateInCurrentSelection ),
TickerList[Date], DESC
)
RETURN
AVERAGEX ( __top4DaysWithValues , TickerList[Close_Price] )
Hope this helps.
Mariusz
Hi @chrisB13 ,
Try the below and let me know if it performs any better.
4 Day Simple Moving Avg = VAR d = MAX(TickerList[Date]) VAR t = TOPN( 4, FILTER( ALL(TickerList[Date]), TickerList[Date] <= d), TickerList[Date], DESC ) RETURN AVERAGEX(t, CALCULATE(SUM(TickerList[Close_Price])))
Hope this helps
Mariusz
Hi @chrisB13,
Please see the below.
4 Day Moving Avg =
VAR __maxDateInCurrentSelection =
MAX ( TickerList[Date] )
VAR __top4DaysWithValues =
TOPN (
4,
FILTER ( ALL ( TickerList ), TickerList[Date] <= __maxDateInCurrentSelection ),
TickerList[Date], DESC
)
RETURN
AVERAGEX ( __top4DaysWithValues , TickerList[Close_Price] )
Hope this helps.
Mariusz
Thank you, slightly worried about the performance of it though. Looks like it hits performance pretty heavily, even trying a 3 day moving average is taking a few minutes to load.
It actually failed to load, ran out of RAM (32 GB)
Not sure why, but I would have to assume it has to do with the ORDER BY portion of the DAX. If we have 3000-4000 rows, it will need to do each row individually and forced to rebuild the Descending order every time.
Hi @chrisB13 ,
Try the below and let me know if it performs any better.
4 Day Simple Moving Avg = VAR d = MAX(TickerList[Date]) VAR t = TOPN( 4, FILTER( ALL(TickerList[Date]), TickerList[Date] <= d), TickerList[Date], DESC ) RETURN AVERAGEX(t, CALCULATE(SUM(TickerList[Close_Price])))
Hope this helps
Mariusz
Thank you, that works much quicker!!
I may be coming back on here for some other calculations, that was the easiest of the calculations so far.
RSI, Stochastics, MACD, Variance, etc... all need to be done
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |