Forum Discussion

Albertvw's avatar
Albertvw
Frequent Visitor
10 years ago

Latest 3 month Index average

If I run an Access ave of latest 3 monht ave index score, I get 82.65, which is correct if I test manually.

 

If I use measurement for sample: =CALCULATE(COUNT(TABLE[INDEX]), DATESINPERIOD(TABLE[MONTH], MAX(TABLE[MONTH], -3, MONTH)) I get the correct sample count for the latest 3 months index sample.

 

However, then using:  =CALCULATE(AVERAGE(TABLE[INDEX], DATESINPERIOD(TABLE[MONTH], MAX(TABLE[MONTH], -3, MONTH)) I get 82.78

 

Why the 'incorrect' ave calculation?

 

Thank you

 

 

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi Albertvw,

     

    In DAX, when a column contains blank values, those rows will not be counted in AVERAGE() function. I think this is the reason why you get a little greater result when using AVERAGE() funciton. Please see: AVERAGE Function (DAX). To avoid this issue, you can use SUM divide by COUNTROWS to get correct result.

     

    Regards,