Measure that calculates a fixed number of values going back

[Anonymous]

I have a report for surveys submitted by customers. It calculates averages on customer responces. Each survey has 8 answers and one average of all answers. There is one measure that adds all responces and divides by the number of questions, I named it 'Survey Average'. Then I created several other measures that calculate the averages for the past year, last 90 months, compares last quarter with current quarter, and latest month, etc.

But.... how do I write a measure that calculates the last 20 surveys? Should it have a counter and loops 20 times? How do I do that?

For example, this is a measure that calculats all surveys from the beginning of 2020:

2020 = CALCULATE([Survey Average], DATESBETWEEN('Date'[Date],DATE(2020,1,1),TODAY()))

[v-alq-msft]

Hi, @Anonymous 

I wonder if you could provide some sample data and expected results with OneDrive for Business. I create sample data to reproduce your scenario.

Table:

You may create two measures as below.

Avg = 
SUMX(
    'Table',
    'Table'[Response]
)/
SUMX(
    'Table',
    'Table'[Question]
)

Visual Control = 
RANKX(
    ALL('Table'),
    CALCULATE(MAX('Table'[Date]))
)

Then you need to put 'Visual Control' in the visual level filter to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Sample data would really help on this one. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

There is a technique for this. You are effectively implementing a while loop in DAX but in a DAX kind of way. So what you do is you add a cumulative counter column where it counts the number of rows that are "less than" the current row. As long as you have a date or an index you can do that. You then can figure out the maximum number you have, subtract 20 and then pull back from your table the correct rows. I use this technique on things like TRIMMEAN, etc. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/m-p/1074075#M504

I can craft it for you but would want sample data and expected output because it is work to put these together and I'd rather just do it once.

[Anonymous]

Here is a sample data set and an explanation of how my project is set up:

I created a measure that calculates average of answers for a metric. The metric name is 'Project' and it includes answers 1 and 2 for survey type Office, and 1 and 10 for survey type Cafeteria.

Project=

 var counter =

COUNTAX(FILTER(FILTER(Survey,Survey[Q1 Value]<>0),Survey[Survey Type]= "Office"),Survey[Q1 Value]) 

+ COUNTAX(FILTER(FILTER(Survey,Survey[Q2 Value]<>0),Survey[Survey Type]= "Office"),Survey[Q2 Value])

+ COUNTAX(FILTER(FILTER(Survey,Survey[Q10 Value]<>0),Survey[Survey Type]= "Cafeteria"),Survey[Q10 Value])

+ COUNTAX(FILTER(FILTER(Survey,Survey[Q1 Value]<>0),Survey[Survey Type]= " Cafeteria "),Survey[Q1 Value])

var sum_ =

SUMX(Filter(Survey,Survey[Survey Type]="Office "),Survey[Q1 Value])

+SUMX(Filter(Survey,Survey[Survey Type]="Office"),Survey[Q2 Value])

+SUMX(Filter(Survey,Survey[Survey Type]="Cafeteria "),Survey[Q10 Value])

+SUMX(Filter(Survey,Survey[Survey Type]=" Cafeteria "),Survey[Q1 Value])

RETURN

IF(counter=0,"N/A",

ROUND(DIVIDE(sum_,counter),2))

I created additional measures to calculate the 'Project' metric based on specific criteria. For example, this is the metric to calculate 6 month average.

Project 6 Month Avg = ROUND(CALCULATE([Project],DATESINPERIOD('Date'[Date],TODAY(),-6,MONTH)),2)

I created an Index column for the table. Each new survey with the most recent date gets a higher index number.  I thought of creating the measure that uses TOPN function based on Index, but it doesn't work properly. Keep in mind that the real data set has a lot more rows than the one I provided below.

Project last 20 surveys = CALCULATE([Project],TOPN(20,Survey,Survey[Index],ASC))

Survey Type

Project Number

Name

Date

Question 1

Q1 Value

Question 2

Q2 Value

Question 3

Q3 Value

Question 4

Q4 Value

Question 5

Q5 Value

Question 6

Q6 Value

Question 7

Q7 Value

Question 8

Q8 Value

Question 9

Q9 Value

Question 10

Q10 Value

Q Answered

Project Name

Project Manager

Q1 Comments

Q2 Comments

Q3 Comments

Q4 Comments

Q5 Comments

Q6 Comments

Q7 Comments

Q8 Comments

Q9 Comments

Q10 Comments

Comments

Index

Office

43

Survey 1

43780

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

0

0

8

Project 1

Jane Doe

1

Cafeteria

54

Survey 2

43831

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

0

9

Project 2

Tom Smith

2

Office

23

Survey 3

43831

Strongly Agree

5

Strongly Agree

5

Agree

4

Strongly Agree

5

Agree

4

Strongly Agree

5

Agree

4

Strongly Agree

5

0

0

8

Project 3

Jason Blue

Good job

Good job

Good job

3

Cafeteria

89

Survey 4

43862

Agree

5

Agree

4

Strongly Agree

5

Agree

4

Strongly Agree

5

Strongly Agree

5

Strongly Agree

5

5

Somewhat Agree

3

NA

0

7

Project 4

Jan Moon

4