Show next row value as the current value

[Anonymous]

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

[v-xuding-msft]

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]

Hi  @v-xuding-msft 

Could you please help fix the difference.

Thank you

Poojitha.

[v-xuding-msft · Accepted solution]

Hi @Anonymous,

Sorry for misunderstanding. Please try this:

 next = LOOKUPVALUE('Table'[Of Bussiness Days],'Table'[Index],'Table'[Index]+1)

[Anonymous]

Hi @v-xuding-msft 

Thank you for the quick turaround and helping me fix an urgent issue.

[Anonymous]

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

[Greg_Deckler]

@Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

[Anonymous]

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