Forum Discussion
Skyman8880
1 year agoFrequent Visitor
cumulative sum by Date and address
Hello PBI forums, I'm a new user, and I'm having issues with developing a Cumulative sum for customer purchases over time. Given fields: Date Address Number (Unique to a customer, can repeat...
- 1 year ago
The code below will produce your desired results from the data you posted.
You'll need to replace the Source line with your actual data source.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEyBJKxOiAhI0whuCojYxOokDGmkCmmRjOYkImpGboQWFUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Address Number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Address Number", Int64.Type}}), //To be able to return to original order #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), //Group by Address Number, then add an Index column to each subtable (starting with `1`) #"Grouped Rows" = Table.Group(#"Added Index", {"Address Number"}, { {"Count", each Table.AddIndexColumn(_,"Order Count", 1, 1, Int64.Type), type table [Date=nullable date, Address Number=nullable number, Index=Int64.Type, Order Count=Int64.Type]}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index", "Order Count"}), //Return to original order //Then remove the index column and set the columns to desired order #"Sorted Rows" = Table.Sort(#"Expanded Count",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Address Number", "Order Count"}) in #"Reordered Columns"
- Skyman88801 year agoFrequent Visitor
This is a fantasitc article! Thank you so much!