Handling cumulative measures in Power Query

[Anonymous]

Dear all,

so I have been following the following tutorial, however I came across a relatively simple problem. I perform my analysis in Excel, however that should not be a problem, with Power Query.

Consider the following table:

Table

We load it in into Power Query by pressing Data > From Table/ Range. Please name this table Table1.

We then add the measure to it, by selecting a cell in the table and then go to Power Pivot > Measures > New Measure...

With the new measure, we insert the following: 

=CALCULATE(SUM(Table1[Amount]), FILTER(Table1, Table1[GL]=EARLIER(Table1[GL])))

It should not matter what the measure name is nor the Table1 that we have selected. Anyways, we will get the following error message: "Calculation error in measure 'Table1'[measure 1]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
".

I simply cannot comprehend why this method does not work and why the tutorial on the site does work. I am not sure what we do differently. I do understand the error and that is that the first row does not have a predecessor, however I am not sure how to solve this problem.

Any help or enlightenment would be highly appreciated.

[HotChilli]

I think your ultimate goal is to follow along with the tutorial and learn from it so you won't be creating a Power Query solution (yes, it's possible but you're not following the tutorial at that point).  You will create a calculated column in Excel and I know that it is possible.

[Anonymous]

Well... I tried that, too, but I get that CALCULATE was not detected in Excel. It seems that Power Query only allows for a M-formula and that the expression they have calculated there is a DAX and there is no way for me to turn it into an M-formula.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You may be a beginner to Power Query. The language written in the screenshot you provided is DAX. In Power Query, we use the M language.

If you want to group and accumulate in Power Query, you can do the following

1.Right click on the blank space and create an empty query.

2.Then in this empty query, click the advanced editor, copy the following formula in it, create a function, and rename the query to fn_cumulative_Total_en.

let
    Source = (Input as table) =>

let
    Sorting = Table.Sort(Input,{{"Date", Order.Ascending}}),                                        // Sort table
    added_Index = Table.AddIndexColumn(Sorting, "Index", 1, 1),                                      // add Index, base 1
    cumulativ_total = Table.AddColumn(                                                               // add new column with running total 
                                            added_Index, "Total", 
                                            each List.Sum(
                                                         List.Range(added_Index[Amount],0,[Index])
                                                        )
                                            ),
    extract_total = cumulativ_total[Total]                                                           // transform new column into list as result for grouping
in
extract_total
in
    Source

3.In the main table, your entire code is as follows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZCxCsAgDET/xdmAJkZ0LIX+hDh0697/hyoKRaNbuMedd6akzvt9lFZo0IAFW05rVNZLYHeAdsAJgICxEhaEgKoHgnzfAZl6R0G4mUhW883jGrmOsZmPSzms5XUG8yT3bTyH0FDy1/usOaZvCjjpfRGWT80f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GL = _t, Date = _t, Amount = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"GL", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
    Calc_Running_Total = Table.Group(
                                      #"Changed Type1",                                                                           // table as table
                                     {"GL"},                                                                       // key as any
                                                                                                                       // aggregatedColumns as list 
                                        {{"Data",                                                                           // name of new column to be created
                                    // Function Call
                                        (Input as table) as table =>                                                        // data for function
                                        let
                                        Call_Function = fn_cumulative_Total_en(Input),
                                    // End of Function Call

                                        result = Table.FromColumns(                                                         // putting all together
                                                                    Table.ToColumns(Input)&{Call_Function},
                                                                        Value.Type(
                                                                                   Table.AddColumn(
                                                                                                    Input, "Saldo", each null, type number
                                                                                                   )
                                                                                )
                                                                       )
                                        in
                                            result,


                                    type table}}                                                                    // transform into table
                                    ),
    #"Expanded Data" = Table.ExpandTableColumn(Calc_Running_Total, "Data", {"Date", "Amount", "Saldo"}, {"Date", "Amount", "Total"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"GL", type text}, {"Date", type date}, {"Amount", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type"

Reference: https://www.powerbi-pro.com/en/grouped-running-total-in-power-query/

Best Regards,

Stephen Tao

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

[HotChilli]

The tutorial is using calculated columns. You are creating measures.  Either way is possible but the measure requires an aggregation in that filter.

So if you're following the tutorial, stick to calculated columns