Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a calculated column that depends on value from previous row in this same column

Hi all, I'm new to PowerBI and having trouble. Here is the data in excel, and I am trying to replicate calculations for columns C and D in PowerBI either through a calculated column or through measures to visually display this. 

Column C

Cell "C2" just copies cell "B2", but when you click into cell "C3" the formula above should calculate (which depends on the previous row C2). This formula is then dragged all the way down until cell "C9". 

Column D

Same process as column 2, until you reach "D6", which copies cell "B6", and then cell "D7" repeats the formula/calculation again. 

 

Also here was my attempt below at trying column C in powerBI. 

 

 

 

Link to Excel Sheet 

Please let me know if anybody can solve this issue.  I've linked the excel sheet for easier access. Thanks

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

     

    It's a List.Accumulate, not sure if I did it too complicated, but it did the job

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTLQM1SK1YFzdQ30DMzQBFAUALkmML4hhG+ExjdD45si80EWAAViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Return = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Return", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Return % based on principal", each List.Accumulate(List.FirstN(#"Added Index"[Return],[Index]), [Return], (state, current) =>if [Index]=0 then [Return] else(1+state)*(1+current)-1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Return % restarted each year", each [
curYear=[Date],
a=Table.SelectRows( #"Added Index", each [Date]=curYear),
b=List.Accumulate(List.FirstN(a[Return],[Index]-List.Min(a[Index])), [Return], (state, current) =>if [Index]=0 or [Date]<>curYear  then [Return] else(1+state)*(1+current)-1)][b] )
in
    #"Added Custom1"

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

     

    It's a List.Accumulate, not sure if I did it too complicated, but it did the job

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTLQM1SK1YFzdQ30DMzQBFAUALkmML4hhG+ExjdD45si80EWAAViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Return = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Return", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Return % based on principal", each List.Accumulate(List.FirstN(#"Added Index"[Return],[Index]), [Return], (state, current) =>if [Index]=0 then [Return] else(1+state)*(1+current)-1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Return % restarted each year", each [
curYear=[Date],
a=Table.SelectRows( #"Added Index", each [Date]=curYear),
b=List.Accumulate(List.FirstN(a[Return],[Index]-List.Min(a[Index])), [Return], (state, current) =>if [Index]=0 or [Date]<>curYear  then [Return] else(1+state)*(1+current)-1)][b] )
in
    #"Added Custom1"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks so much! Do you mind explaining the code a bit? I'm assuming that List.FirstN returns the entire return list. What is the significance of making the seed equal to the return column? Also, wouldn't the state be the accumulation up to that value, and the current be the current value? If so, how does that match up with the original formula used to calculate the percentage? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous 

         

        I was too focused on the list, actually no need to check the Index or year anymore. List.FirstN was used to return the Return list, correct, but not all, only return all previous Return - the ones before current Return, so state = current Return, current goes over all previous Return, hope it makes sense...

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTLQM1SK1YFzdQ30DMzQBFAUALkmML4hhG+ExjdD45si80EWAAViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Return = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Return", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Return % based on principal", each List.Accumulate(List.FirstN(#"Added Index"[Return],[Index]), [Return], (state, current) =>(1+state)*(1+current)-1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Return % restarted each year", each [
curYear=[Date],
a=Table.SelectRows( #"Added Index", each [Date]=curYear),
b=List.Accumulate(List.FirstN(a[Return],[Index]-List.Min(a[Index])), [Return], (state, current) =>(1+state)*(1+current)-1)][b] )
in
    #"Added Custom1"