Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
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
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
66 | |
60 |