Forum Discussion

rendalignacio's avatar
rendalignacio
Icon for Helper I rankHelper I
8 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 a new table?

 

Need Help

6 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 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
      8 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
        8 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] ))
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Share the link from where i can download your file.  Also, please show the expected result.

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

      Hi,

       

      Sorry, I cannot share the pbix as there are a lot of files in it.

       

      But, for the result, what i would want is to show the average of the last 3 months per aircraft registry.