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?

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!