cumulative sum by Date and address

[Skyman8880]

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...
But I'm having trouble with modifying the code to work on my own.

[RMPhaniKumar]

Create a new callucated column and use below DAX

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

[v-saisrao-msft]

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]

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.

[ronrsnfld · Accepted solution]

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]

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

[Deku]

Please see this article

[Skyman8880]

This is a fantasitc article!  Thank you so much!