Add a column value with max of previous rows of current column

[touseef2660]

Hello, I want to write a calculated column dax in which I need to add other column  value with current column previous rows max value. Check this example in excel: 

In this, I am adding column A (Gross) value with the max of previous rows of column B.  Is this possible in Power BI calculated column?
I tried a formula given by @Dangar332 but it is not giving correct values. Check below:

 You can find the sample files here: Power BI

Please help me with this tricky problem. Thanks in advance

@amitchandak @Ashish_Mathur 

[touseef2660]

@Greg_Deckler  Can you help me in this problem please?

[Greg_Deckler]

Pretty certain @lbendlin is correct on this one. What you have here is something that requires a "previous value" of a calculation and that's just not possible in DAX as DAX despises anything that even smells like recursion. 

[touseef2660]

I got it. Thank you for responding. I really appreciate it. 

[touseef2660]

@amitchandak  Can you please help in this problem?

[Ahmedx]

add a second column like this

[touseef2660]

But this is also not giving correct output. If you check in excel the output should be:

[lbendlin · Accepted solution]

Your Excel formula is wrong.  Should say $B$2.

I don't think you can do this in DAX as this would require a self referencing MAXX condition inside a SUMX.

It is relatively trivial in Power Query as List.Accumulate allows for the manipulation of intermediate results.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlCK1YlWMgaTpmDSCEzqGoIpZNIEpjQWAA==", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Gross = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Gross", Int64.Type}}), 
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
  #"Added Custom" = Table.AddColumn(
    #"Added Index", 
    "Calculation", 
    each List.Accumulate(
      {0 .. [Index]}, 
      [s = 0, m = 0], 
      (state, current) => [
        s = #"Added Index"[Gross]{current} + state[m], 
        m = List.Max({#"Added Index"[Gross]{current} + state[m], state[m]})
      ]
    )
  ), 
  #"Expanded Calculation" = Table.ExpandRecordColumn(
    #"Added Custom", 
    "Calculation", 
    {"s"}, 
    {"Calculation"}
  ), 
  #"Removed Other Columns" = Table.SelectColumns(#"Expanded Calculation", {"Gross", "Calculation"}), 
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Removed Other Columns", 
    {{"Calculation", Int64.Type}}
  )
in
  #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[touseef2660]

Thank you for your reply but if it could be possible to solve in dax that would be really helpful. So you are saying this is not possible through Dax right?

[lbendlin]

To the best of my knowledge this is not possible in DAX. You would need to be able to self reference a calculated table while it is being constructed. Happy to be proven wrong.