Forum Discussion

chrisB13's avatar
chrisB13
Frequent Visitor
7 years ago
Solved

Historical Stock Data Concept

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 symbo...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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




  • Mariusz's avatar
    Mariusz
    7 years ago

    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

     

chrisB13's avatar
chrisB13
Frequent Visitor
7 years ago

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.

 

 

Mariusz's avatar
Mariusz
Community Champion
7 years ago

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

 

  • chrisB13's avatar
    chrisB13
    Frequent Visitor
    7 years ago

    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