Trailing Average & Previous Quarter Average

[Anonymous]

I have this data:

 

What I'm trying to do with this data is:

1. Figure out how to make trailing 12 month average on my [Positive %] measure. It is a measure that simply goes through my fact table and counts the number of what's considered positive feedback:

 

2. Figure out how to calculate what the average positive % was for the previous quarter (Qtr 1) using the trailing 12 month averages that have been calculated.

I have an inactive relationship setup between my fact and date table. It's set up this way so other formulas from other tables are not impacted. I've tried setting up a 1 to many relationship, but it causes other pieces to break so I'd rather not do it that way if I can help it. Thanks!

[v-jiascu-msft]

Hi @Anonymous,

Please refer to Desktop/Trailing-12-Month-Average. It will give you some ideas. Can you share a dummy sample? Since the [Positive %] is a measure, I can't recreate it in my test.

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

I attempted to attach a sample of my data but I don't see a place on here to attach spreadsheets. Here's a screenshot of what I'm working with:

So the breakdown:

1. These are survey results. The columns are questions that were asked in the survey. Values are the answers from the users.

2. I was first tasked with grouping and counting everything that is considered a "positive" response. Positive responses consist of Strongly Agree, Agree, Happy, Very Happy.

3. In order to achieve this, I took all columns with these values andn unpivoted them so everything was in one column. This makes it easier to count.

4. So to get the positive %, I had one measure that counts the "positive values" and one measure that counts all values whether they are positive, negative or neutral. Then simply did [Positive Count/Total Count].

5. From here, I am stuck on getting what the average % is:

 

It keeps coming out 100% no matter how I set it up. After I figure out how to get the overall average, I then need to figure out how to have this as a trailing 12 month average so I can see how it is trending over time. Then the last step using my trailing 12 month average numbers, is getting the overall average for the previous quarter.