Need help in calculating POS with mentioned conditions. Files are attached.

[Anonymous]

 Please find the sample file in below location:
https://drive.google.com/drive/folders/1Ut7HHoIQmuvU8qj-U2vC8Tid-Mooz9qY?usp=sharing

@amitchandak 
Need help in creating this complex measure.

Condition:

Formula is POS= Top1-nve cumulative + pve Cumulative

But if the result is negative then we replace the value with 0
and for the next row formula has to be POS= 0- POS_nve +POS_pve
Suppose if we do not get negative value for the above row then cumulation has to start from that point.

Please find the expecetd result column from the excel sheet attached

[lbendlin · Accepted solution]

You cannot do this as a measure. You cannot even do this as a calculated column.  The only way you can do that is in Power Query.

Table.AddColumn(
  #"Added Index", 
  "Result", 
  each List.Accumulate(
    {1 .. [Index]}, 
    #"Added Index"[POS Top1]{0}, 
    (state, current) =>
      List.Max({0, 
                state + #"Added Index"[POS_pVE]{current} - #"Added Index"[POS_nVE]{current}
               })
  )
)

See attached.

[Anonymous]

Please suggest if this can be solved by a measure. I cannot implement the solution in power query. 

[lbendlin · Accepted solution]

You cannot do this as a measure

Please let me know how I can formulate that statement more convincingly.  SUMX does not allow for conditional resets. Only List.Accumulate has that feature.

[lbendlin · Accepted solution]

You cannot do this as a measure. You cannot even do this as a calculated column.  The only way you can do that is in Power Query.

Table.AddColumn(
  #"Added Index", 
  "Result", 
  each List.Accumulate(
    {1 .. [Index]}, 
    #"Added Index"[POS Top1]{0}, 
    (state, current) =>
      List.Max({0, 
                state + #"Added Index"[POS_pVE]{current} - #"Added Index"[POS_nVE]{current}
               })
  )
)

See attached.