Subtract previous days using M-Query

[FPP]

Hi, I have a table that contains (Date, Category and Sales)

Date	Category	Sales
2024-01-01	A	300
2024-01-01	B	400
2024-01-02	A	1000
2024-01-02	B	500
2024-01-03	A	1600
2024-01-03	B	800

In the above Table (within a category)
1) the Jan 2 Sales numbers are Jan 1 and Jan 2 added together.

2) the Jan 3 Sales numbers are Jan 1, Jan 2, Jan 3 added together.

I want a table that has the Actual Sales

Date	Category	Sales	Actual Sales
2024-01-01	A	300	300
2024-01-01	B	400	400
2024-01-02	A	1000	700
2024-01-02	B	500	100
2024-01-03	A	1600	600
2024-01-03	B	800	300

[wdx223_Daniel]

[v-saisrao-msft]

Hi @FPP 

May I ask if you have resolved this issue? If so, please mark their reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @FPP,

Could you please confirm if your query have been resolved the solution provided by @Omid_Motamedise & @p45cal ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[Omid_Motamedise · Accepted solution]

Hi @FPP 
use this formula

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MAQiJR0lRyA2NjBQitVBk3ACYhN0CSOoDkMDbDIgLaboEsYwLWbYZEBaLEASsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Sales]-(List.Sum(Table.SelectRows(#"Changed Type", (x)=>x[Date]<[Date] and x[Category]=[Category])[Sales])??0))
in
    #"Added Custom"

[slorin]

Hi @FPP

let
Source = Your_Source,
Sort = Table.Sort(Source,{{"Category", Order.Ascending}, {"Date", Order.Ascending}}),
Group = Table.Group(Sort, {"Category"}, 
      {{"Data", 
        each Table.FromColumns(
          {[Date], 
           [Sales], 
           List.Transform(List.Zip({[Sales], {0} & List.RemoveLastN([Sales],1)}), each _{0}-_{1})},
          {"Date", "Sales", "Actual Sales"}),
        type table [Date=nullable date, Sales=nullable number, Actual Sales=nullable number]}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Date", "Sales", "Actual Sales"}, {"Date", "Sales", "Actual Sales"})
in
Expand 

 Stéphane

[p45cal]

(In haste) not especially efficient:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTJR0lRyA2NjBQitVBk3ACYhOYhBGaDkMDbDIgLaYwCWN0LWbYZEBaLEASsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Sales = _t]),
    fnMonthlyActualSales = (tbl)=>
    let
        SortedRows = Table.Sort(tbl,{{"Date", Order.Ascending}}),
        AddedIndex = Table.AddIndexColumn(SortedRows, "Index.1", -1, 1, Int64.Type),
        AddedCustom = Table.AddColumn(AddedIndex, "Actual Sales", each [Sales] - (if [Index.1]<0 then 0 else AddedIndex[Sales]{[Index.1]})),
        RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"Date", "Category", "Sales", "Actual Sales", "Index"})
    in
        RemovedOtherColumns,
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Sales", type number}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    InsertedEOM = Table.AddColumn(AddedIndex, "End of Month", each Date.EndOfMonth([Date]), type date),
    GroupedRows = Table.Group(InsertedEOM, {"Category", "End of Month"}, {{"grp", each _, type table [Date=nullable date, Category=text, Sales=number, End of Month=date]}}),
    InvokedCustomFunction = Table.AddColumn(GroupedRows, "MonthlyActualSales", each fnMonthlyActualSales([grp])),
    RemovedColumns = Table.RemoveColumns(InvokedCustomFunction,{"grp", "End of Month"}),
    ExpandedMonthlyActualSales = Table.ExpandTableColumn(RemovedColumns, "MonthlyActualSales", {"Date", "Sales", "Actual Sales", "Index"}, {"Date", "Sales", "Actual Sales", "Index"}),
    SortedRows = Table.Sort(ExpandedMonthlyActualSales,{{"Index", Order.Ascending}}),
    ReorderedColumns = Table.ReorderColumns(SortedRows,{"Date", "Category", "Sales", "Actual Sales", "Index"}),
    RemovedColumns1 = Table.RemoveColumns(ReorderedColumns,{"Index"}),
    ChangedType1 = Table.TransformColumnTypes(RemovedColumns1,{{"Date", type date}})
in
    ChangedType1

It treats the data on a monthly basis, so assumes first sale per month is not a cumulative figure.