Calculating 3 months per registry

[rendalignacio]

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?

[Ashish_Mathur]

Hi,

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

[rendalignacio]

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.

[Ashish_Mathur]

Hi,

Share a dummy dataset.

[v-sihou-msft]

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

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]

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