Change Cumulative Sums back to differences

[ShamR9T]

I have a data source in the following form

Sales Person	Location	Item	1-Jan-23	8-Jan-23	15-Jan-23	22-Jan-23
A	1	Z	1	1	2	2
B	2	X	0	0	1	2
C	3	Y	0	1	2	3
A	3	X	1	5	7	7
B	2	Y	0	0	0	0
C	1	Z	0	0	0	0

It is an aggregation for each sales person where their sales has been and the item and it shows progressively how many total sales they have made by each day. EG: Sales Person A has sold 1 X at location 3 on 1 January and by 8 January this number increased to 5 which means they have sold 4 more items.

I need to change this data to the following format. I have been able to unpivot the dates and create the first 5 columns but done know how to create the sales each day column. Wouldnt mind a calculated column or a power query to achieve this.

Sales Person	Location	Item	Date	Cumulative Sum	Sales Each Day
A	1	Z	1/1/2023	1	1
A	1	Z	1/8/2023	1	0
A	1	Z	1/15/2023	2	1
A	1	Z	1/22/2023	2	0
B	2	X	1/1/2023	0	0
B	2	X	1/8/2023	0	0
B	2	X	1/15/2023	1	1
B	2	X	1/22/2023	2	1
C	3	Y	1/1/2023	0	0
C	3	Y	1/8/2023	1	1
C	3	Y	1/15/2023	2	1
C	3	Y	1/22/2023	3	1
A	3	X	1/1/2023	1	1
A	3	X	1/8/2023	5	4
A	3	X	1/15/2023	7	2
A	3	X	1/22/2023	7	0
B	2	Y	1/1/2023	0	0
B	2	Y	1/8/2023	0	0
B	2	Y	1/15/2023	0	0
B	2	Y	1/22/2023	0	0
C	1	Z	1/1/2023	0	0
C	1	Z	1/8/2023	0	0
C	1	Z	1/15/2023	0	0
C	1	Z	1/22/2023	0	0

Sorry for the messy title and long explanations. Hope the issue is clear though.

Thank you

[rubayatyasmin · Accepted solution]

Hi, @ShamR9T 

there was some miss calculations in the previous solution. 

the solution is okay now. 

There is some workaround in this query that you need to modify. 

Here is the PQ code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WCEgtKs7PU9JR8slPTizJBDM9S1JzgZShrldinq6RMZBpgWAamiLYRkYwdqxOtJIjSBaIo6A0CBuBMUjWCcqLAGIDKDaEyzoDWSATI1FkQGIwk42hekEypkBsDsbIJkcimQzBMJNhrkKTjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Person", type text}, {"Location", Int64.Type}, {"Item", type text}, {"1-Jan-23", Int64.Type}, {"8-Jan-23", Int64.Type}, {"15-Jan-23", Int64.Type}, {"22-Jan-23", Int64.Type}}),
   // Step 2: Unpivot the date columns
    UnpivotedColumns = Table.UnpivotOtherColumns(#"Changed Type1", {"Sales Person", "Location", "Item"}, "Date", "Value"),

   // Step 3: Group by Sales Person, Location, Item, and Date
    GroupedTable = Table.Group(UnpivotedColumns, {"Sales Person", "Location", "Item", "Date"}, {{"Cumulative Sum", each List.Max([Value]), type number}}),
    AddIndex = Table.AddIndexColumn(GroupedTable, "Index", 0, 1, Int64.Type),

     AddPreviousDaySales = Table.AddColumn(AddIndex, "Previous Day Sales", each if [Index] > 0 then AddIndex{[Index]-1}[Cumulative Sum] else 0),
    AddSalesEachDay = Table.AddColumn(AddPreviousDaySales, "Sales Each Day", each [Cumulative Sum] - [Previous Day Sales]),
    
    // Step 4: Remove unnecessary columns
    RemovedColumns = Table.RemoveColumns(AddSalesEachDay,{"Index", "Previous Day Sales"})
in
    RemovedColumns

try to understand the values that is showing minus, the calculation is alright with the given logic. You wan to subtract values with the previous values. You need to adjust it a little bit. But this should be very helpful. 

[rubayatyasmin]

Hi, @ShamR9T 

Is this what you are looking for?

find the solution file

https://file.io/NKZpL7JQlvX6

[ShamR9T]

Hi @rubayatyasmin 

Sorry, this is not. if you look at the sales each day, it still shows it as the same value as the cumulative sum. I need the value for each product to subtract from the previous days value. 

For example:
Sales Person A

Location 1

Product Z

1st Jan is 1

8 Jan is 1 

Hence 1 Jan sales each day should be 1 

but 8 Jan should be 0 since its still 1 on 8 Jan.