Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show next row value as the current value

Hi All,

I have a very urgent requirement. Source data looks as below:

 

 

Requirement is - I need to create a new column based on  # of Business Days. The condition for the column is that for each Submission Number (Col A) , the value for the current row should be the value of the  # of Business Days of the next row, eg : 

 

 the last step will always be blank.

Can someone please suggest how this can be achieved

Thank you

Poojitha

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

      Thank you for the share but my requirement is different, where I need to show the previous value as the current value grouped by submission number.

      Thank you

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please try this:

     

    1. Create an index column
    2. create a calculated column using this:
    Column = 
var next = LOOKUPVALUE('Table'[Of Bussiness Days],'Table'[Index],'Table'[Index]+1)
var diff = next-'Table'[Of Bussiness Days]
return
IF(ISBLANK(next),BLANK(),IF(diff <=0,0,diff))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-xuding-msft ,

       

      Thank you for the logic, but I tried the same but noticed that the values are differnt in the new column.

      The value is getting substracted by 1.
      0 should be 1 and 17 should be 18.

       

      Could you please suggest the fix.

       

      Thank you

      Poojitha

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi  v-xuding-msft 

      Could you please help fix the difference.

      Thank you

      Poojitha.