Support needed with the calculated column

Hi Josan009​ 

please try apparoch suggested by ronrsnfld​ .

If it doesnt work please try below mcode

let
    Source = Table.FromRows(
        {
            {#date(2025,4,26), "B", 54},
            {#date(2025,4,4),  "C", 12},
            {#date(2025,1,23), "A", 57},
            {#date(2025,5,18), "B", 61},
            {#date(2025,1,23), "C", 79},
            {#date(2025,3,20), "B", 37},
            {#date(2025,1,3),  "C", 86},
            {#date(2025,4,27), "C", 67},
            {#date(2025,3,14), "A", 93},
            {#date(2025,1,3),  "B", 51},
            {#date(2025,1,3),  "A", 11},
            {#date(2025,2,17), "A", 95},
            {#date(2025,2,25), "C", 32},
            {#date(2025,4,5),  "A", 25},
            {#date(2025,2,11), "B", 76}
        },
        type table [Date = date, SKU = text, QTY = Int64.Type]
    ),

    // Running total per SKU, ordered by Date
    Grouped = Table.Group(
        Source,
        {"SKU"},
        {{"Rows", (grp) =>
            let
                Sorted  = Table.Sort(grp, {{"Date", Order.Ascending}}),
                Qty     = List.Buffer(Sorted[QTY]),
                Running = List.Generate(
                    () => [i = 0, total = Qty{0}],
                    each [i] < List.Count(Qty),
                    each [i = [i] + 1, total = [total] + Qty{[i] + 1}],
                    each [total]
                ),
                Result  = Table.FromColumns(
                    Table.ToColumns(Sorted) & {Running},
                    Table.ColumnNames(Sorted) & {"Cumulative QTY"}
                )
            in
                Result,
          type table [Date = date, SKU = text, QTY = Int64.Type, Cumulative QTY = Int64.Type]
        }}
    ),

    Combined = Table.Combine(Grouped[Rows]),
    FinalSort = Table.Sort(Combined, {{"SKU", Order.Ascending}, {"Date", Order.Ascending}})
in
    FinalSort

Instead if Table.Rowcount i am trying to use List.Count.Let me know if it works.

Please give kudos or mark it as solution once confirmed.

Regards,

Praful

Hi Josan009​,

Can you provide some more details? 

Please provide a sample of what you expect the output to be.  

Not sure how you want the final result presented, but this can be done in Power Query by 

1. Group by SKU
2. Sort by date within each subgroup
3. Add a running total column
4. Sort how you prefer and expand the results.

Here is the M code which you can paste into the Advanced Editor. Be sure to change the first line to reflect your actual data source.

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"SKU", type text}, {"QTY", Int64.Type}}),

//Group by SKU, then sort by date and add a Running total Column
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {
        
        {"RT", (t)=>
            [a=Table.Sort(t, {"Date", Order.Ascending}),
             b=List.Generate(
                 ()=>[idx=0, rt=a[QTY]{0}],
                 each [idx] < Table.RowCount(t),
                 each [idx=[idx]+1, rt = [rt] + a[QTY]{idx}],
                 each [rt]
                 ),
            c=Table.FromColumns(
                Table.ToColumns(a) & {b},
                {"Date","SKU", "QTY", "Running Total"}
            )][c], type table[Date=date, SKU=text, QTY=Int64.Type, Running Total=Int64.Type]            
        }}),
    
//Sort by SKU if necesssary
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"SKU", Order.Ascending}}),

//Remove no longer needed column
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"SKU"}),

//Expand the nested tables
    #"Expanded RT" = Table.ExpandTableColumn(#"Removed Columns", "RT", {"Date", "SKU", "QTY", "Running Total"})
in
    #"Expanded RT"

Thanks, I really appreciate your support

Just one more follow-up question: I am new to M Code. Can anyone suggest a platform where I can learn about M Code 

Hi Josan009​ ,

Thank you for reaching out to fabric community.

Here are few Microsoft documentations where you can learn M Code.

Power Query M function reference - PowerQuery M | Microsoft Learn

Quick tour - PowerQuery M | Microsoft Learn

Power Query M formula language introduction - PowerQuery M | Microsoft Learn

Thanks!!

Thanks, I really appreciate your support

Yes,Since the data is already loaded into the Data Model, create a calculated column in DAX:

Cumulative Qty = VAR CurrentSKU = 'Table'[SKU] VAR CurrentDate = 'Table'[Date] RETURN CALCULATE( SUM('Table'[QTY]), FILTER( 'Table', 'Table'[SKU] = CurrentSKU && 'Table'[Date] <= CurrentDate ) )

This will calculate the cumulative QTY separately for each SKU, sorted by Date.

For example, SKU B:

Date SKU QTY Cumulative

1/3/2025 B 51 51

2/11/2025 B 76 127

3/20/2025 B 37 164

4/26/2025 B 54 218

5/18/2025 B 61 279

Replace 'Table' with your actual table name.