Help getting units sold per day from cummulative units sold.

[Juarente]

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

[latimeria · Accepted solution]

@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]

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"