Recursive calculation issue using previous_value

[Anonymous]

Hi,

I am trying to migrate one of my dashboard from Tableau to PowerBI. I need to calculate Cumulative backlog in such a way that Cumulative backlog is sum of daily backlog column and can not go negative. I achieved it using following formula in Tableau.

IF (PREVIOUS_VALUE(SUM([Daily Backlog])) + SUM([Daily Backlog])) <0
then 0 ELSE
(PREVIOUS_VALUE(SUM([Daily Backlog])) + SUM([Daily Backlog]))
END

I am sharing an example table showing the expected outcome for "Cumulative Backlog" column below. 

Since PREVIOUS_VALUE function doesn't exist in PowerBI, how can i achieve the same result. I tried many options but PowerBI shows a loop error. Please help.

Date	Daily Backlog	Cumulative Backlog
1-Mar	81	81
2-Mar	-50	31
3-Mar	-40	0
4-Mar	3	3
5-Mar	30	33
6-Mar	25	58
7-Mar	-60	0
8-Mar	-14	0
9-Mar	-25	0
10-Mar	2	2
11-Mar	74	76
12-Mar	53	129
13-Mar	-11	118
14-Mar	-42	76
15-Mar	31	107
16-Mar	-82	25
17-Mar	38	63

[Vijay_A_Verma · Accepted solution]

Inset an Index column with 1 start and use below formula in a custom column

= List.Accumulate(List.FirstN(#"Added Index"[Daily Backlog],[Index]),0,(s,c)=> List.Max({0,s+c}))

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7BDcAgCIXhXThrIhWUDtEJjIeu0P0PTYDn8Qug/1rE9Xk/KmRMuyy6klWbu8MSlnR3KRTDkbzUOXE7Ymwwi/uGc58b7oNIm7HOaNP4nE8bRzvLic0XTl8uILBaLiCxG+39Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Daily Backlog" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Daily Backlog", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Bufferd List" = List.Buffer(#"Added Index"[Daily Backlog]),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Cumulative Backlog", each List.Accumulate(List.FirstN(#"Bufferd List",[Index]),0,(s,c)=> List.Max({0,s+c}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

[Anonymous]

Thanks Vijay_A_Verma for sharing the solution.

I am doing this calculation using parameters on the frontend rather than in the backend. 

Hence, solution you provided using powerquery is not going to work. Can you suggest an alternate way of achieving it using DAX?