Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks for your help
Elnaz
Solved! Go to Solution.
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}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "column3", each [Column1]-[Column2]),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate(List.Range(#"Added Index" [column3],0,[index]),0,(x,y)=>if x+y<0 then 0 else x+y))
in
#"Added Custom"
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
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}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "column3", each [Column1]-[Column2]),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate(List.Range(#"Added Index" [column3],0,[index]),0,(x,y)=>if x+y<0 then 0 else x+y))
in
#"Added Custom"
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
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])
I really appreciate your time
Elnaz
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |