Forum Discussion

PB_MZ's avatar
PB_MZ
Regular Visitor
3 years ago

Trailing 6 months average

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.

DAX: Trailing average

5 Replies

    • PB_MZ's avatar
      PB_MZ
      Regular Visitor
      3 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      PB_MZ
      Regular Visitor
      3 years ago

      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:

       

       

    • PB_MZ's avatar
      PB_MZ
      Regular Visitor
      3 years ago

      This is the results I would like to get.