Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
65 | |
63 | |
56 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |