Forum Discussion
Solved
Help getting units sold per day from cummulative units sold.
Hello there,
My data looks like this:
| Product_ID | Date | Cummulative_Sold_Units | Previous_Date |
| 89067 | 22-may-22 | 110 | 21-may-22 |
| 86567 | 22-may-22 | 123 | 21-may-22 |
| 45678 | 22-may-22 | 78 | 21-may-22 |
| 89067 | 23-may-22 | 115 | 22-may-22 |
| 86567 | 23-may-22 | 129 | 22-may-22 |
| 45678 | 23-may-22 | 83 | 22-may-22 |
| 89067 | 24-may-22 | 115 | 23-may-22 |
| 86567 | 24-may-22 | 129 | 23-may-22 |
| 45678 | 24-may-22 | 83 | 23-may-22 |
As you can see, I have dayly data for each product sales in units, however, those are cummulative units, meaning the qty of units sold since the product begun selling.
I added a new column (Prevous Date) in the hope that I could get de cummulative qty of units sold the day before, then substract both quantyties and get something like this:
| Product_ID | Date | Cummulative_Sold_Units | Previous_Date | Cumm_on_Previous_Date | Units_Sold |
| 89067 | 22-may-22 | 110 | 21-may-22 | null | null |
| 86567 | 22-may-22 | 123 | 21-may-22 | null | null |
| 45678 | 22-may-22 | 78 | 21-may-22 | null | null |
| 89067 | 23-may-22 | 115 | 22-may-22 | 110 | 5 |
| 86567 | 23-may-22 | 129 | 22-may-22 | 123 | 6 |
| 45678 | 23-may-22 | 89 | 22-may-22 | 78 | 11 |
| 89067 | 24-may-22 | 120 | 23-may-22 | 115 | 5 |
| 86567 | 24-may-22 | 129 | 23-may-22 | 129 | 0 |
| 45678 | 24-may-22 | 97 | 23-may-22 | 89 | 8 |
I haven't been able to acomplish this with a DAX formula for a new column in Power Query. Hope someone can help me here. Thanks.
Regards,
Juan
Juarente ,
Another way to sort out the problem
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5RCsAgCIDhu/hcUGalZwkfOsAOsNtPgoE12Jv44Y9jAEtqHQIgxmveEdHmnBNoMGv1a1iWkREfZot19iaLT9Y96Q1lTzpj2ZPkz44v6SfpTDqoPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Date = _t, Cummulative_Sold_Units = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Cummulative_Sold_Units", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Product_ID"}, {{"Row", each _, type table [Product_ID=nullable text, Date=nullable date, Cummulative_Sold_Units=nullable number]}}), #"Sort by date" = Table.TransformColumns( #"Grouped Rows",{ "Row", each Table.Sort(_, {{"Date", Order.Ascending}})} ), #"Added Index" = Table.TransformColumns( #"Sort by date",{ "Row", each Table.AddIndexColumn(_, "Index",0)} ), #"Added Day quantity" = Table.AddColumn(#"Added Index", "Custom", each let Group = [Row], DayQty = Table.AddColumn( Group, "Qty", each try Group[Cummulative_Sold_Units]{[Index]} - Group[Cummulative_Sold_Units]{[Index]-1} otherwise 0 ) in DayQty), Expand = Table.Combine( #"Added Day quantity"[Custom], {"Product_ID", "Date", "Cummulative_Sold_Units","Qty"}), #"Changed Type1" = Table.TransformColumnTypes(Expand,{{"Product_ID", Int64.Type}, {"Date", type date}, {"Qty", Int64.Type}, {"Cummulative_Sold_Units", Int64.Type}}), #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Product_ID", Order.Descending}}) in #"Sorted Rows1"
2 Replies
latimeriaSolution Specialist
Juarente ,
Another way to sort out the problem
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5RCsAgCIDhu/hcUGalZwkfOsAOsNtPgoE12Jv44Y9jAEtqHQIgxmveEdHmnBNoMGv1a1iWkREfZot19iaLT9Y96Q1lTzpj2ZPkz44v6SfpTDqoPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Date = _t, Cummulative_Sold_Units = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Cummulative_Sold_Units", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Product_ID"}, {{"Row", each _, type table [Product_ID=nullable text, Date=nullable date, Cummulative_Sold_Units=nullable number]}}), #"Sort by date" = Table.TransformColumns( #"Grouped Rows",{ "Row", each Table.Sort(_, {{"Date", Order.Ascending}})} ), #"Added Index" = Table.TransformColumns( #"Sort by date",{ "Row", each Table.AddIndexColumn(_, "Index",0)} ), #"Added Day quantity" = Table.AddColumn(#"Added Index", "Custom", each let Group = [Row], DayQty = Table.AddColumn( Group, "Qty", each try Group[Cummulative_Sold_Units]{[Index]} - Group[Cummulative_Sold_Units]{[Index]-1} otherwise 0 ) in DayQty), Expand = Table.Combine( #"Added Day quantity"[Custom], {"Product_ID", "Date", "Cummulative_Sold_Units","Qty"}), #"Changed Type1" = Table.TransformColumnTypes(Expand,{{"Product_ID", Int64.Type}, {"Date", type date}, {"Qty", Int64.Type}, {"Cummulative_Sold_Units", Int64.Type}}), #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Product_ID", Order.Descending}}) in #"Sorted Rows1"- Vijay_A_Verma
Most Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5RCsAgCIDhu/hcUGalh9gJwoeOsdtPgoE12Jv44Y9jAEtqHQIgxmveEdHmnBNoMGv1a1iWkREfZot19iaLT9Y96Q1lTzpj2ZPkz44v6SfpTDqoPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Date = _t, Cummulative_Sold_Units = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product_ID", Int64.Type}, {"Date", type date}, {"Cummulative_Sold_Units", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Previous Date", each Date.AddDays([Date],-1)), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Product_ID", "Previous Date"}, #"Added Custom", {"Product_ID", "Date"}, "Added Custom", JoinKind.LeftOuter), #"Added Custom1" = Table.AddColumn(#"Merged Queries", "Previous **bleep** Units Sold", each try [Added Custom]{0}[Cummulative_Sold_Units] otherwise null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Added Custom"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Units_Sold", each [Cummulative_Sold_Units]-[Previous **bleep** Units Sold]) in #"Added Custom2"
