cancel
Showing results for
Did you mean:
Regular Visitor

## Recursive calculations in the calculated column after filtering data based on date value

Hi all,

For each subgroup in a group, I want to calculate a column based on this formula:

New column(n) = max(0,new column(n-1)+ column1(n) - column2(n)

In this formula n refers to the current row, n-1 refers to previous row according to the the date column

Actually I can do the following calculation using SUMX in CALCULATION and FILTER expression in dax:

New column(n)=new column(n-1)+ column1(n)-column2(n)

However, my main problem is in implementing the max(0, "calculation")

In fact by imposing this maximum I want to put a lower limit 0 on my calculated values.

Elnaz

1 ACCEPTED SOLUTION
Community Support

Hi @Elnaz91 ,

Please try to do it in power query.

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

in

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

Best Regards,

Dedmon Dai

2 REPLIES 2
Community Support

Hi @Elnaz91 ,

Please try to do it in power query.

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

in

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

Best Regards,

Dedmon Dai

Regular Visitor

Thanks a million it helped me alot.

How ever I am dealling with other problem as follows:

Newcolumn[n]=MAX(0,MAX(0,Newcolumn[n-1]-column1[n])+column2[n])

Elnaz

Announcements