Power Bi - Get the average of the last X rows from a table

Question

I am currently in the process of learning myself, Power Bi. However, I got myself stuck when I tried to summarize a result based on the last X rows instead of filtering the last X dates. In order to have a better understanding here is the table that I created:

table, th, td {
  border: 1px solid black;
}

Table name: Exampe<br><br>
<table><tbody><tr><th>Period</th><th>Value</th></tr><tr><td>2021 - W1</td><td>56.48%</td></tr><tr><td>2021 - W2</td><td>54.58%</td></tr><tr><td>2021 - W3</td><td>61.18%</td></tr><tr><td>2021 - W4</td><td>61.86%</td></tr><tr><td>2021 - W5</td><td>18.18%</td></tr><tr><td>2021 - W6</td><td>89.81%</td></tr><tr><td>2021 - W7</td><td>52.85%</td></tr><tr><td>2021 - W8</td><td>41.69%</td></tr><tr><td>2021 - W9</td><td>18.18%</td></tr><tr><td>2021 - W10</td><td>75.54%</td></tr><tr><td>2021 - W11</td><td>61.48%</td></tr><tr><td>2021 - W12</td><td>48.48%</td></tr></tbody></table>

Where the result I am looking after should be the average percentage based on the last 6 rows instead of the entire column rows. Therefore, I ended up using "Measure" as I was not able to find a pre-build function for this in Power BI. My formula is the following:

Measure = Calculate(Average(Exampe[Value],TOPN(6,Exampe,[Value],Desc)

However, the output is incorrect. I would be really grateful if you can help me understand what I did wrong and why the formula is giving the wrong output. Thank you in advance!

Answer 1

Assuming you have the table as following, and you would like to get the average of last 6 row which is 750

In order to achieve the average last 6 row, first you need to create index column in query editor, after that using the following measure can achieve the expected output 750

Measure = CALCULATE(AVERAGE(Sheet1[Value]),
            FILTER(ALL(Sheet1),Sheet1[Index] <= MAX(Sheet1[Index])
                 && Sheet1[Index] >MAX(Sheet1[Index]) -6))

This is how the table in Power Bi look like with index col, accept if help :)