Forum Discussion

Mharris's avatar
Mharris
Frequent Visitor
4 years ago
Solved

Rolling 6 Month Average (new column)

Hello,

I am trying to show the rolling 6 month average in a new column (Column with orange values) shown in the screen shot below.   I need to get the 85% shown which is taking the average from 6/1/2021 - 1/1/2021 (6 months).  

I can't seem to get the DAX logic to work with the new column so I was hoping someone would have some ideas.

 

Thank you!

 

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Mharris 

    This is similar to typical moving average. Few  questions. The date column from which table? Is it end of month date or month name? What is the dax code of the average measure?

  • Mharris's avatar
    Mharris
    Frequent Visitor
    4 years ago

    tamerj1 so this is a basic excel dumby data screen shot so the date column is just called month and I manually filled out the data shown by month.  I don't have any dax code of the average measue yet, ideally I want to know what that dax code would be to populate a new column where the 85% is showing so I can use that in a more complex setting down the road.

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Mharris 

      Sorry I meant to ask about the percentage measure no the average. Is it a measure or a column in the raw data? Or this some you need to calculate? The month contains month names or start of month dates? Are looking for a measure or a calculated column?

      • Mharris's avatar
        Mharris
        Frequent Visitor
        4 years ago

        tamerj1 No problem at all - the percentage measure in that screen shot is a manual measure inputted.  I created a simpy excel with those % numbers so I could try and get the dax logic that would give me the previous 6 month average which is that 85% column.  The month field is also a manual input and I just did months to make it easier to test the dax logic.  Ideally, I will calculate a new measure field in my real data that will produce a specific % and once I get that % similar to the screen shot %, I will take the average of the previous 6 months of that %.  Let me know if this makes sense.

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    You can do it with easier with the help of a date table. Please see file. I hope you can accept as solution. attached.https://drive.google.com/file/d/1oyxlgh8wvRb2y5eNg8MiTBgkwge5k-zR/view?usp=sharing 

     

    Six Month MA =
    VAR indexmonth = 'Table'[Month Index]

    VAR _measure =
    CALCULATE(AVERAGEX( DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]),-6, MONTH), CALCULATE(SUM('Table'[Percent]))))
    RETURN
    IF(indexmonth >= 6, _measure, BLANK())