Problems with COUNT

[elandry9840]

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

[tamerj1]

Hi @elandry9840 

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

[Anonymous]

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]

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!

[elandry9840]

Sure thing! Thanks for the reply!!

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

[tamerj1]

@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]

@elandry9840 

Have you tried above code?

[elandry9840]

Yes. Thank you, but doesn't seem to work. Must be something in my model that is peculiar. 

[elandry9840]

Sorry. Here it is:

[tamerj1]

@elandry9840 

Would you please add the moving average measure and snother measure: COUNTROWS ( 'PriceTable')

[elandry9840]

Thank you so much for the help! I have to step out for an hour, but will be back after that and will reply promptly to whatever you need. 

[tamerj1]

@elandry9840 

I apologize as well. It was too late and I fell asleep. 
Are you sure you have a relationship. The results are not filtered at all. There is something wrong here

[elandry9840]

Thanks. I went back and deleted them then recreated them. Still no luck. 

[tamerj1]

If possible, can you share a sample file?

[elandry9840]

Sure. How do I do that?

[tamerj1]

You can upload for example to We Transfer, DropBox or OneDrive and share the link

[tamerj1]

@elandry9840 

Please use the variable name inside FILTER same as the code I have shared

[elandry9840]

Replied to above

[elandry9840]

This is better:

[elandry9840]

Best: