Rolling 6 Month Average (new column)

[Mharris]

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!

[Whitewater100]

Your welcome. Glad it worked for you:-)

[Whitewater100 · Accepted solution]

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())

[Mharris]

This was super helpful @Whitewater100 ! Thank you.

[Mharris]

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

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

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

[tamerj1]

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?