Averaging a Measure

[Anonymous]

I have a table with survey results. I also have a date table with an inactive realationship setup between the 2 using the survey date from the survey results table and the date from the table. The Month End column below is from my date table and the positive % measure is from my survey results table:

 

My positive % measure consist of these pieces:

 

A certain set of values represents positive feedback (happy, very happy, agree , strongly agree). Anything else would either be considered netural or negative feedback (neutral, unhappy, very unhappy, disagree, strongly disagree). So the focus here is, out of all feedback given, what % represents positive feedback. Months where there are no %'s just means no surveys were taken that month.

I was asked to turn this data into a trailing 12 month average, but I'm not sure how. I had success with a different set of data by turning my measure into a column then building my trailing 12 month average measure, but it is not working with this scenario. When I turn the above measure into a column so I can use it with the AVERAGE function, it returns the total count of "positive" feedback on each row rather than the percentages:

With it coming out this way, I'm unable to build my trailing 12 month retention formula. It returns 100% for every month and I know that's not right. Any ideas of how I can set this up?

[v-juanli-msft]

Hi @Anonymous

Could you please illustrate "trailing 12 month average" with examples? Do you mean you want to get the averate from 1/31/2017 to 1/31/2018 on 1/31/2018, and get average from 3/31/2017 to 3/31/2018 on 3/31/2018, etc? 

How about trying this formula

Measure2 = AVERAGEX(SUMMARIZE(ALL Survey Data, ALL Survey Data [MonthEnd], "12month", [ positive %]), [ positive %])

Additionally, here is a similar post.

Calculate Average Trailing 12 Months

Best Regards

Maggie

[Anonymous]

@v-juanli-msft yes, that's exactly what I mean! One small issue. My [Month End] column is coming from my Date table, not my All Survey Data table. How could I modify it to reference that? Or should I just make a month end column from my All Survey Data table? I wasn't sure if it was possible since there are gaps everywhere in that table with the dates.