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)

 


I'm attempting to create a cumulative value for my customer purchases.  Where the first order from a new address will end up with a value of 1, and any further orders will add to the value.


I've looked into the forums and found:
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/cumulative-sum-by-date-and-product/m-p/1973888#M43427
But I'm having trouble with modifying the code to work on my own.

  • 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"

7 Replies

    • Skyman8880's avatar
      Skyman8880
      Frequent Visitor

      This is a fantasitc article!  Thank you so much!

  • 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"
    • Skyman8880's avatar
      Skyman8880
      Frequent Visitor

      Thank you so much for your help!  Kudos all around for all!!!

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support

    Hi Skyman8880,

    Thank you for Reaching out to the Microsoft Fabric Community Forum.

     

    After reviewing your issue, I can confirm that the solution provided by ronrsnfld is a great fit for your scenario. The approach using Power Query to calculate the cumulative order count through grouping and indexing is suited for your dataset.

    To make it even easier for you, I’ve attached a sample PBIX file for your reference. Feel free to explore it and let me know if you have any questions.

     

    Also, thank you Deku for sharing your valuable insights.

    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

     

    Thank you.

    • Skyman8880's avatar
      Skyman8880
      Frequent Visitor

      Thank  you for your help.  I was able to integrate the code from ronrsnfld and I loved the website that was linked from Deku . 

      The only remaining issue is on my own machine.  The Dataset is not exactly small, so it's lots of cycles to get the data updated.

  • Create a new callucated column and use below DAX

    Newcount =
    RANKX(
       FILTER('Table', 'Table'[Column1] = EARLIER('Table'[Column1])),
       'Table'[Column1],,
       ASC,
       DENSE
    )