Cumulative total that reset when is negative

[planc7]

Hi everyone,

I'm opening this thread because the following doesn't give me the answer 

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Total-with-Reset-on-Negat...

I'm really struggling in calculating a running total column that resets on negative value:

in particular when the running total is negative it should result in 0, then the running total should start calculating the total from this point to the next negative value and so on.

This is a sample dataset. Can someone help me in calculating the column "Desired Output"? It's driving me crazy.

In excel, it can be easily managed with this formula (IF(A3+B2<0;0;A3+B2)) but i'm really struggling in PowerBi.

Thank you!!

Week	Delta	Desired Output
202334	5	5
202335	6	11
202340	4	15
202345	1	16
202347	1	17
202348	-37	0
202349	-42	0
202350	-16	0
202351	-59	0
202352	4	4
202401	2	6
202402	-19	0
202403	19	19
202404	35	54

[Ashish_Mathur]

Hi,

Will you be OK with a calculated column formula solution?

[planc7]

Hi, yes. I need a calculated column, not a measure to use in a matrix. Thank you in advance!

[Ashish_Mathur]

Hi,

With the clarification that ou have issued to v-xuxinyi-msft, i do not even know how to solve it with a calculated column formula. 

[planc7]

According to you, can it be managed with 2 columns maybe? Or if it were in PowerQuery?

[Anonymous · Accepted solution]

Hi @planc7 

You can try using the following method in PowerQuery.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/LDcAgDEN34QxSyKeUWRD7r9G4qmqO71mJk7WKipp5qSXKrh9G4vWjS6ITkXbiwKyQ7+Rmg2JCuP4isK91FkSHiEmhZ6MLciXqOz8pDBcdjHfyib0f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Delta = _t]),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Week", Int64.Type}, {"Delta", Int64.Type}}
  ),
  FX = (values as list) as list =>
    let
      GRTList = List.Generate(
        () => [GRT = values{0}, i = 0],
        each [i] < List.Count(values),
        each 
            let
                nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1}
            in
                [GRT = nextGRT, i = [i] + 1],
        each [GRT]
      )
    in
      GRTList,
  weeklist = List.Buffer(#"Changed Type"[Week]),
  deltalist = List.Buffer(#"Changed Type"[Delta]),
  result = Table.FromColumns(
    {Source[Week], Source[Delta], List.Transform(FX(deltalist), each if _ < 0 then 0 else _)},
    {"Week", "Delta", "Output"}
  ),
    #"Changed Type1" = Table.TransformColumnTypes(result,{{"Week", Int64.Type}, {"Delta", Int64.Type}, {"Output", Int64.Type}})
in
  #"Changed Type1"

Output:

Best Regards,
Yulia Xu

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

[planc7]

Brilliant!! Thank you very much! 🙂

[Anonymous]

Hi @planc7 

Please try the following.

Create calculated columns as follow

Index = RANKX('Table', [Week], , ASC, Dense)

Output = 
VAR _EarlierIndex = 'Table'[Index]
VAR _EarlierDelta = CALCULATE(SUM('Table'[Delta]), FILTER('Table', [Index] = _EarlierIndex - 1 && [Delta] > 0))
RETURN
IF([Delta] < 0, 0, _EarlierDelta + [Delta])

Output:

Best Regards,
Yulia Xu

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

[planc7]

Hi,

thank you for your answer! 

Please, I need a calculated column not a measure. In addition, the logic that you wrote seems not matching my need.

The value should be 0 only if the partial running total is negative, not the value on delta. This is another sample dataset that shows you that even if I have a negative value on delta, the running total is diminshing til the value become negative. I highlight in red what i mean.

Thank you again for your time!

Week		Delta		Desired Output
202334		5		5
202335		6		11
202340		4		15
202345		1		16
202347		50		66
202348		-37		29
202349		-42		0
202350		-16		0
202351		-59		0
202352		4		4
202401		2		6
202402		-19		0
202403		19		19
202404		35		54