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

     

Mariusz's avatar
Mariusz
Community Champion
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




chrisB13's avatar
chrisB13
Frequent Visitor
7 years ago

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.

  • 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