Subtracting yesterday´s value from today´s when several items exist per day

[up60]

Hello community,

I have table with several products which stores the accumulated sales of that product on each day. i.e., the total sales of that product since the beginning of the month are stored everyday. (sample below)

Product	Day	Total Sales Since beginning of Month
X	01/11/2023	10
Y	01/11/2023	15
Z	01/11/2023	9
X	02/11/2023	18
Y	02/11/2023	21
Z	02/11/2023	16
X	03/11/2023	32
Y	03/11/2023	30
Z	03/11/2023	27

I am looking to create a new column "Daily Sales" and store the daily sales for each product as shown below -

Product	Day	Total Sales Since beginning of Month	Daily Sales
X	01/11/2023	10	10
Y	01/11/2023	15	15
Z	01/11/2023	9	9
X	02/11/2023	18	8
Y	02/11/2023	21	6
Z	02/11/2023	16	7
X	03/11/2023	32	14
Y	03/11/2023	30	9
Z	03/11/2023	27	11

Can someone please help with this?

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczLDYAwDIPhXXyu1MQVr02AKPuvAcolKRz9yfrNcKJBtKt2Csc7VODNcP18Cb+/fgRHhvW+Z6Y6NTPTf83OKD6YncklO9W5wf0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Day = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Day", type date}, {"Total", Int64.Type}},"en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Daily", each  try [Total]- Table.Sort(Table.SelectRows(#"Changed Type",(k)=> [Product]=k[Product] and k[Day]<[Day]),{"Day",Order.Descending}){0}[Total] otherwise [Total],Int64.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[up60]

Hi @lbendlin ,

This works, thank you very much.