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"
ronrsnfld
Super User
1 year agoThe 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"Skyman8880
1 year agoFrequent Visitor
Thank you so much for your help! Kudos all around for all!!!