Subtract previous days using M-Query

(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.

Hi Anonymous

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

Hi Anonymous,

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

Hi Anonymous 

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.