Forum Discussion

NeroTolentino's avatar
NeroTolentino
Frequent Visitor
5 years ago
Solved

Q > Calculated Column using record from previous row

Hello,

 

Help! I need to add a calculated column using this formula. Any thoughts on how to specifically do this?

 

Thanks!

 

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi NeroTolentino ,

     

    Please do like this.

    1. Add an [Index] column in 'Edit Query'.

    2. Create such a calculated column.

    Column 2 = 
VAR __A = 
CALCULATE(
    MAX(Sheet1[A]),
    FILTER( Sheet1, Sheet1[Index] = 1 )
)
VAR __Pre_B = 
CALCULATE(
    SUM(Sheet1[B]),
    FILTER( Sheet1, Sheet1[Index] <= EARLIER(Sheet1[Index]) )
)
VAR __Pre_C = 
CALCULATE(
    SUM(Sheet1[C]),
    FILTER( Sheet1, Sheet1[Index] <= EARLIER(Sheet1[Index]) )
)
RETURN
IF(
    [Index] = 1,
    __A + [B] - [C],
    __A + __Pre_B - __Pre_C
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    5 years ago

    You can leverage index columns to do this - import the data twice, one with an index column starting at 0 and another with it starting at 1, then merge in the way that the previous row in one data set will map to the current row in the other. Should be a real simple calculation after that

  • NeroTolentino's avatar
    NeroTolentino
    Frequent Visitor
    5 years ago

    jthomson  , lit2018pbi  ;

     

    Thanks for looking into this. I did both but I think it's missing an extra step.

     

    I cannot simply do the earlier and index trick since each rows in that column is dependent on the result of the previous row. 

     

    I hope the image below will help. I need to create a Calculated Column (column D) in the picture, using the formula in column E.

     

     

     

     

    • v-lionel-msft's avatar
      v-lionel-msft
      Community Support
      5 years ago

      Hi NeroTolentino ,

       

      Please do like this.

      1. Add an [Index] column in 'Edit Query'.

      2. Create such a calculated column.

      Column 2 = 
VAR __A = 
CALCULATE(
    MAX(Sheet1[A]),
    FILTER( Sheet1, Sheet1[Index] = 1 )
)
VAR __Pre_B = 
CALCULATE(
    SUM(Sheet1[B]),
    FILTER( Sheet1, Sheet1[Index] <= EARLIER(Sheet1[Index]) )
)
VAR __Pre_C = 
CALCULATE(
    SUM(Sheet1[C]),
    FILTER( Sheet1, Sheet1[Index] <= EARLIER(Sheet1[Index]) )
)
RETURN
IF(
    [Index] = 1,
    __A + [B] - [C],
    __A + __Pre_B - __Pre_C
)

       

      Best regards,
      Lionel Chen

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.