Trailing 6 months average

[PB_MZ]

Hi,

I would like to calculate the trailing 6 month average for Margin % (A), Margin % (B), Margin % (C). They are measures instead of columns.

I tried several ways but none of them worked for me. I am wondering if I can find some luck here. Thank you in advance.

[Anonymous]

Hi @PB_MZ ,

I think you can refer to below code to create measures.

Trailing 6 months average for Margin % (A) =
VAR _EndDate =
    MAX ( DimDate[Date] )
VAR _StartDate =
    EOMONTH ( _EndDate, -7 ) + 1
RETURN
    AVERAGEX (
        FILTER (
            ALL ( DimDate ),
            DimDate[Date] >= _StartDate
                && DimDate[Date] <= _EndDate
        ),
        [Margin % (A)]
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PB_MZ]

This is the results I would like to get. 

[PB_MZ]

Hi,

Thank you for your response. But unforturnately, I didn't get the results I want by your way. Correct me if I was wrong.

This is the code:

And this is the result from your code:

[FreemanZ]

hi @PB_MZ 

what do you mean by "trailing 6 month average"?

[PB_MZ]

Hi,

Sorry for the late resposne. Trailing 6 months average means I need to calculate the average of (previous 5 months + current month) for each month.