Forum Discussion

rendalignacio's avatar
rendalignacio
Icon for Helper I rankHelper I
9 years ago

Calculating 3 months per registry

Hi,   I need help on the table that I have. I want to get the 3 month (Aug, Jul and Jun) average per registry of the average table.   Can you help me in providing the DAX measurement or if i need...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

rendalignacio

 

If you want to calculate the moving 3 months average,you can write a measure like below:

 

Moving 3 months avg=
CALCULATE (
    AVERAGE ( Table[DSR] ),
    ALLEXCEPT ( Table, Table[Registry] ),
    FILTER ( ALL ( Table ), Table[MonthYearNumber] )
        > ( MAX ( Table[MonthYearNumber] ) - 3 )
        && Table[MonthYearNumber] <= MAX ( Table[MonthYearNumber] )
)

Regards,

  • rendalignacio's avatar
    rendalignacio
    Icon for Helper I rankHelper I
    9 years ago

    Hi,

     

    From the formula you have provided, it gives me an error "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

     

    Also, can i place this in a table?

     

    Thank you

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

      rendalignacio

       

      Sorry, there's an incorrect bracket. 

       

      Updated:

       

      Moving 3 months avg=
CALCULATE (
    AVERAGE ( Table[DSR] ),
    ALLEXCEPT ( Table, Table[Registry] ),
    FILTER ( ALL ( Table ), Table[MonthYearNumber] 
        > ( MAX ( Table[MonthYearNumber] ) - 3 )
        && Table[MonthYearNumber] <= MAX ( Table[MonthYearNumber] ))
)