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.
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
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 |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |