Forum Discussion

Skyman8880's avatar
Skyman8880
Frequent Visitor
1 year ago
Solved

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...
  • ronrsnfld's avatar
    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"