Forum Discussion

elandry9840's avatar
elandry9840
Frequent Visitor
4 years ago

Problems with COUNT

Hi, First post here. This should be simple for an experienced DAX person, and I'd greatly appreciate a suggestion or two. I have a table with weekly stock closing prices for a few thousand tickers. ...
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi elandry9840 

how does your report look like? Can you share a screenshot of your data model?

  • elandry9840's avatar
    elandry9840
    Frequent Visitor
    4 years ago

    Sure thing! Thanks for the reply!!

     

    Not sure if this will be big enough. If not, let me know. 

    • elandry9840's avatar
      elandry9840
      Frequent Visitor
      4 years ago

      This is better:

       

       

      • elandry9840's avatar
        elandry9840
        Frequent Visitor
        4 years ago

        Best:

         

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      elandry9840 

      The screenshot is very small. However please try

      Number of Stocks < 200Day MA =
      VAR FortyWeekMA = [40-week MA]
      RETURN
      COUNTROWS ( FILTER ( 'PriceTable', PriceTable[closeadj] < FortyWeekMA ) )

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        elandry9840 

        Have you tried above code?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi elandry9840 ,

     

    Not sure, is the first measure wrong? 
    Please use this measure and test.

    40-week MA =
CALCULATE (
    AVERAGE ( PriceTable[closeadj] ),
    FILTER (
        'Calendar',
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date]
                >= MAX ( 'Calendar'[Date] ) - 200
    )
)

    Attached PBIX file for reference.

     

    Best Regards,

    Gao
    Community Support Team 

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    • elandry9840's avatar
      elandry9840
      Frequent Visitor
      4 years ago

      Hi v-cgao-msft,

       

      Appreciate the reply!! I'm not sure it's working tho. Take a look:

       

      40-week MA (New) =
      CALCULATE (
      AVERAGE ( PriceTable[closeadj] ),
      FILTER (
      'Calendar',
      'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
      && 'Calendar'[Date]
      >= MAX ( 'Calendar'[Date] ) - 200
      )
      )
       
      40-week MA =
      AVERAGEX(
      DATESBETWEEN('Calendar'[Date], max('Calendar'[Date]) - 200, max('Calendar'[Date])),
      calculate(sum(PriceTable[closeadj]))
      )
       
      Looks like [40-Wk MA (New)] is returning the closeadj. See below:
       

       

      Could it have something to do with the price table having data only once per week? I noticed your price table had dailt prices. 

       

      Thanks again!