Previous value in the same column Power Bi-CodePudding

If it possible to somehow refer to the previous value in the same column? I know there is option to use it in next column. But it not really fit for me because I need some logic like this. If (calculation value>check value; yes value; previous value). I got error " A cyclic reference was encountered during evaluation " When I am trying refer back.

CodePudding user response：

IF I understand what you want to do correctly, you can accomplish that with the List.Generate function. You generate a list according to your rules; then combine it with the original table.

M Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWJBpI6SsZgljmQZQJmGQFZlmCWMZBlBGaZAVlmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),

//create conditional adding column 
   c1 = #"Changed Type"[Column1],
   c2 = #"Changed Type"[Column2],
    conditionalAdd = List.Generate(
            ()=>[res= if c1{0}   c2{0} > 10 then c1{0} c2{0} else 0, idx=0],
            each [idx] < List.Count(c1),
            each [res=if c1{[idx] 1}   c2{[idx] 1} > 10 then c1{[idx] 1}   c2{[idx] 1} else [res],idx=[idx] 1],
            each [res]),

    //combine with original table
    newTable = 
        Table.FromColumns(
            Table.ToColumns(#"Changed Type") & {conditionalAdd},
            Table.ColumnNames(#"Changed Type") & {"Conditional Add"}
        )
in
   newTable

Source

newTable