Forum Discussion
Anonymous
1 year agoNot applicable
Subtract previous days using M-Query
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 160...
- 1 year ago
Hi Anonymous
use this formulalet 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"
p45cal
1 year agoSolution Supplier
(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
ChangedType1It treats the data on a monthly basis, so assumes first sale per month is not a cumulative figure.