Running total using mutiple filters

Hi Anonymous - I love Power Query, but this is best done in DAX I believe as was your original course. Here is what I have done:

You can see it matches your expected "Cumulative Goal" values. Full measure text below.

Mine uses variables Anonymous. The EARLIER() function is no longer needed and is not recommended. It returns the same results, and 3 years ago, was the only way to do it. But variables, the VAR/RETURN construct, is easier to read and edit, and can actually improve performance where you might user EARLIER() multiple times in a measure.

Anonymous is correct that you should NOT use this in a calculated column. You should avoid them as a rule. 

You can do this in Power Query, but it is not easy, and must use the advanced editor. Here is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOyS9OBdIGhvpAZGRgZADkGIJxrE60khOQ5V+QmoeuAoJBKpxhhijgNgXFIiMkJUZAbAxXArPICNOQWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domian = _t, Descr = _t, Date = _t, Count = _t, Cumulative = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Count", Int64.Type}, {"Cumulative", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Domian", "Descr"}, 
            {
                {
                    "All Rows", 
                    each 
                        let
                            varCurrentTable = _
                        in
                        Table.AddColumn(
                            _,
                            "Cumulative Total",
                            each
                                let
                                    varCumulativeDate = [Date]
                                in
                                    List.Sum(
                                        Table.SelectRows(
                                            varCurrentTable,
                                            each [Date] <= varCumulativeDate
                                        )[Count]
                                    )
                        ), 
                    type table [Domian=nullable text, Descr=nullable text, Date=nullable date, Count=nullable number, Cumulative=nullable number, Cumulative Total = nullable number]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Count", "Cumulative", "Cumulative Total"}, {"Date", "Count", "Cumulative", "Cumulative Total"})
in
    #"Expanded All Rows"

If you need this in a column, then yes, by all means, use this code. It returns the red column, next to your "Cumulative" goal in the sample.

I cannot guarantee this will be performant over hundreds of thousands, or millions of records. That is where DAX excels and Power Query tends to bog down, but over 10K records or so, no problem. Give it a try.

I would appreciate KUDOS for any posts in this thread that have helped you, and if one of my answers is the solution, please mark it as such. See the very bottom of this post for some reasons not to use a calculated column. Below are directions to use this code in Power Query if you are not accustomed to pasting raw code in.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query