cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Measure that calculates a fixed number of values going back

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()))
3 REPLIES 3
Community Support

Hi, @Kinga99

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.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Announcements