Conditional running total on categories

Hi PQRookie 

You can create a blank query and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1ACJjJR0lRyCOTC0GkoYGSrE6SLImUFm/fJCkKaqkKVDMCadWMxSDjdBkzaF6wQaDJGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t, condition = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"category", type text}, {"condition", type text}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[category]=[category] and x[condition]="Yes" and x[date]<=[date])[value]))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

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

Here's another method that relies on Grouping rather than 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1ACJjJR0lRyCOTC0GkoYGSrE6SLImUFm/fJCkKaqkKVDMCadWMxSDjdBkzaF6wQaDJGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t, condition = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"date", type date}, {"category", type text}, {"condition", type text}, {"value", Int64.Type}}),

//add index column to preserve original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Add conditional running total column for each group
    #"Grouped Rows" = Table.Group(#"Added Index", {"category"}, {

        {"Result", (t)=>
            let 
                runTotal = List.Generate(
                    ()=>[rt=if t[condition]{0}="Yes" then t[value]{0} else 0, idx=0],
                    each [idx] < Table.RowCount(t),
                    each [rt=if t[condition]{[idx]+1} = "Yes" then [rt]+t[value]{[idx]+1} else [rt], idx=[idx]+1],
                    each [rt]),
                addCol = Table.FromColumns(
                        Table.ToColumns(t) &
                        {runTotal}, {"date","category","condition","value","Index","result"})
            in 
                addCol, 
            type table [date=nullable date, category=nullable text, condition=nullable text, 
                        value=nullable number, Index=Int64.Type, result=nullable number]}}),
    #"Expanded Result" = Table.ExpandTableColumn(#"Grouped Rows", "Result", {"date", "condition", "value", "Index", "result"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Result",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Works very well an very compact indeed. Impressive.

Thanks and all the best!

Also this one works very well and useful not the least for my learning as well, thanks a lot!