Forum Discussion
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
- Skyman8880Frequent Visitor
This is a fantasitc article! Thank you so much!
- ronrsnfld
Super User
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"- Skyman8880Frequent Visitor
Thank you so much for your help! Kudos all around for all!!!
- v-saisrao-msft
Community 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.
- Skyman8880Frequent Visitor
- RMPhaniKumarNew Member
Create a new callucated column and use below DAX
Newcount =
RANKX(
FILTER('Table', 'Table'[Column1] = EARLIER('Table'[Column1])),
'Table'[Column1],,
ASC,
DENSE
)