topic Re: RE: Shifting of Return Values One Row Above in DAX Commands and Tips

RE: Shifting of Return Values One Row Above

Anonymous — Thu, 24 Nov 2022 01:46:16 GMT

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

Re: RE: Shifting of Return Values One Row Above

amitchandak — Wed, 23 Nov 2022 09:17:15 GMT

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

Re: RE: Shifting of Return Values One Row Above

Anonymous — Thu, 24 Nov 2022 01:29:08 GMT

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

Re: RE: Shifting of Return Values One Row Above

FreemanZ — Thu, 24 Nov 2022 02:48:33 GMT

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?

Re: RE: Shifting of Return Values One Row Above

Anonymous — Thu, 24 Nov 2022 02:51:14 GMT

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.

Re: RE: Shifting of Return Values One Row Above

FreemanZ — Thu, 24 Nov 2022 02:54:11 GMT

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

Re: RE: Shifting of Return Values One Row Above

Anonymous — Thu, 24 Nov 2022 02:57:36 GMT

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.

Re: RE: Shifting of Return Values One Row Above

FreemanZ — Thu, 24 Nov 2022 03:07:13 GMT

aha, then try to replace

VAR _MinIndex = _Index - 30

with

VAR _MinIndex = MIN( _Index - 30, 3)

Re: RE: Shifting of Return Values One Row Above

Anonymous — Thu, 24 Nov 2022 03:24:31 GMT

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

Re: RE: Shifting of Return Values One Row Above

Anonymous — Thu, 24 Nov 2022 04:03:01 GMT

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

Re: RE: Shifting of Return Values One Row Above

Anonymous — Tue, 29 Nov 2022 07:42:45 GMT

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

Re: RE: Shifting of Return Values One Row Above

Anonymous — Tue, 29 Nov 2022 08:19:09 GMT

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