RE: Shifting of Return Values One Row Above

[Anonymous]

Dear all,
I would like to enquire on how I can modify the following formula such that the returning values under, "Rolling 30 Mean" can be shown one row above? (Example: Index 3 is supposed to have the value of, "5.00" under the column, "Rolling 30 Mean", etc but has to appear as the first row in the Table).

Any help is much appreciated deeply as I have been stuck for quite a while.

Thank you.
- Kylx

[Anonymous · Accepted solution]

Hi @Anonymous,

Current power bi data model table does not include row and column index, so it does not support recursive calculations similar like excel expression.

If you want to iterator the previous result, you need to get the previous row index at the first. Then you can use all function to ignore the current filters, manually use index to find out previous field values to reproduce the previous filter and use them as conditions in calculations expressions.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous ,
Thank you for taking the time to reply to my query. I was able to adapt an alternative sample measure which turns out to be what I needed from @Anonymous 's query that was posted from 2-3 years ago. Hence, the issue in this post is solved already.
- KylxT

[FreemanZ]

don't understand this part "Index 3 is supposed to have the value of, "5.00" under the column, "Rolling 30 Mean", etc but has to appear as the first row in the Table).". Which number do you expect for Index 3?

[Anonymous]

According to the images, the first row in the Table is Index 2 but I do not want that shown. Hence, Index 3 is supposed to be the first row in the Table seen and the value under, "Rolling 30 Mean" is 5.00. I hope this clarifies.

[FreemanZ]

Aha, it looks like a table visual, you can choose not to show Index 2 on the filter pane, or?

[Anonymous]

That is one of the ways that I can hide Index 2's row but the Table visual will show Index 3's Rolling 30 Mean as 2.00 instead of 5.00 and that's the issue I'm facing right now.

[FreemanZ]

aha, then try to replace

 

VAR _MinIndex = _Index - 30

 

with 

 

VAR _MinIndex = MIN( _Index - 30, 3)

[Anonymous]

That formula changes all values under the, "Rolling 30 Mean" column which becomes incorrect and the starting index becomes 4 instead of 3.

[Anonymous]

I tried the modification of formula that you mentioned and nothing seemed to have changed on the screen 😅

[amitchandak]

@Anonymous , Use all in place allselected. Make sure this index and week are part of separate table 

check this example

Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [measure]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[Anonymous]

Dear Mr @amitchandak ,
I have tried out the solution that you have proposed but the return values would've changed or that the values under the, "Rolling 30 Mean" does not shift upwards as per expected. Thank you for taking the time out to suggest a solution for me nonetheless.

- Kylx