Column with accumulated values

[Anonymous]

Hello all,

I am working on a task where I need to create a column that acumen the value in the last 2 months. For a specific reason, I cannot use a measure to acumen this number because I only need to accumulate those numbers in a specific context, so from my humble knowledge I intend to solve my issue this way.

Start of Month	SKU	Quantity	Sales Order Invoiced Qty	Sales Order Qty	ERROR	ERROR 2M
01 October 2022	Product 01	641	223	223	418	418
01 November 2022	Product 01	1923	1277	1277	646	1064
01 December 2022	Product 01	1313	700	700	613	1259
01 June 2022	Product 013	400			400	400
01 August 2022	Product 013	641			641	1041
01 July 2022	Product 013	629			629	1270

The result I need to achieve is the last column "ERROR 2M".

Basically, I need this accumulation on the last 2 months (based on the Start of the Month) but considering the distinction of different products (Column SKU).

I know this can be easy for you guys, but for me, it has been I big challenge for days.

[ronrsnfld · Accepted solution]

It seems you are defining "last two months" as relating solely to the row position. Otherwise, why is the last row "1270" and not "1029"?  Or are the dates in "Start of Month" in the wrong order?

To obtain the output you show from the input you provide, try the code below:

let
    Source = Excel.CurrentWorkbook(){[Name="Table32"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"Start of Month", type date}, {"SKU", type text}, {"Quantity", Int64.Type}, 
            {"Sales Order Invoiced Qty", Int64.Type}, {"Sales Order Qty", Int64.Type}, 
            {"ERROR", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {
        {"Error 2m", (t)=> let 
            #"2MRT" = List.Generate(
                ()=>[rt=t[ERROR]{0}, idx=0],
                each [idx] < Table.RowCount(t),
                each [rt=t[ERROR]{[idx]} + t[ERROR]{[idx]+1}, idx=[idx]+1],
                each [rt]),
            #"add 2MRT" = Table.FromColumns(
                    Table.ToColumns(t) & {#"2MRT"},
                    Table.ColumnNames(#"Changed Type") & {"ERROR 2M"}
            )
            in #"add 2MRT",
             type table [Start of Month=nullable date, SKU=nullable text, 
                 Quantity=nullable number, Sales Order Invoiced Qty=nullable number, 
                 Sales Order Qty=nullable number, ERROR=nullable number, ERROR 2M=nullable number]}}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"SKU"}),
    #"Expanded Error 2m" = Table.ExpandTableColumn(#"Removed Columns", "Error 2m", 
        {"Start of Month", "SKU", "Quantity", "Sales Order Invoiced Qty", "Sales Order Qty", "ERROR", "ERROR 2M"})
in
    #"Expanded Error 2m"

[Anonymous]

Hi @ronrsnfld , before anything else, thanks for all your support!
It may be a mistake on my end, but it is not working for me.

I followed your instructions:

1 - Made the table as in the above picture,

2 - Got data from this new table (what was automatically named as "table"),

3 - In the Power Query editor - Pasted your Code (only changing the table reference name to "table"),

4 - I got the error in the screenshot below:

Sincerely,

Jorge Lacerda

[ronrsnfld]

Did you include the "ERROR 2M" column in your original table?

[Anonymous]

Before anything thanks so much for your help! It is much valuable for me!

Sorry! My bad! I typed the wrong month in the table. The last value 1270 is the sum of 641 + 629 (July and August) respectively. I put the table corrected below!:

Start of Month	SKU	Quantity	Sales Order Invoiced Qty	Sales Order Qty	ERROR	ERROR 2M
01 October 2022	Product 01	641	223	223	418	418
01 November 2022	Product 01	1923	1277	1277	646	1064
01 December 2022	Product 01	1313	700	700	613	1259
01 June 2022	Product 013	400			400	400
01 July 2022	Product 013	641			641	1041
01 August 2022	Product 013	629			629	1270

Do you think your code above still can solve my issue?

[ronrsnfld]

Yes

[Anonymous]

Sure! That was the problem! My bad! It is working beautifully!

Thanks so much! you saved me!