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. I want to calc the 40-week moving average for each ticker on each week, then count the number of tickers trading below their moving average for each week. Simple, but I can't get the last part...the count. 

 

Here is the MA calc, which works perfectly:

 

40-week MA =
AVERAGEX(
DATESBETWEEN('Calendar'[Date], max('Calendar'[Date]) - 200, max('Calendar'[Date])),
Calculate(sum(PriceTable[closeadj]))
)
 
To count the number trading below the MA I'm using:
 
Number of Stocks < 200Day MA =
COUNTROWS(
filter('PriceTable',
PriceTable[closeadj] < [40-week MA]
)
)
 
This returns nothing, unfortunately. 
 
The model is simple, with only 1 fact table (weekly pricetable) and two DIM tables (Calendar and Tickers). Can somebody please suggest what I'm doing wrong?
 
Thanks
 

 

19 Replies

    • 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. 

    • 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!