Get Sum of Current and Previous for each Customer

[HeirsPowerBi]

Hey guys.

I have a little challenge.

I have a table like the one below with customers and their orders.

Customer	Order ID	Amount	Date
1	23	5000	01/09/2022
1	24	6000	02/09/2022
1	25	7000	03/09/2022
1	26	3000	04/09/2022
2	27	3900	01/09/2022
2	28	4000	02/09/2022
2	29	2900	03/09/2022
2	30	1000	04/09/2022

I would like to create two additional columns that will tell me the order id of the customers last purchase and then the sum of the current purchase and the previous one as well. So the output should be something like the table below

Customer	Order ID	Amount	Date	Previous ID	Plus Previous
1	23	5000	01/09/2022
1	24	6000	02/09/2022	23	11000
1	25	7000	03/09/2022	24	13000
1	26	3000	04/09/2022	25	10000
2	27	3900	01/09/2022
2	28	4000	02/09/2022	27	7900
2	29	2900	03/09/2022	28	6900
2	30	1000	04/09/2022	29	3900

I can do this DAX but i want to be able to do it in Power Query because there are some other steps i need to do that depend on this one.

I would really appreciate any help. Thanks

[amitchandak]

@HeirsPowerBi ,

Edited: I Noticed @Anonymous  Already replied. Only refer if that solution does not work

Create a new column  like

new column=

Var _last = sumx(filter(Table, Table[Customer] =earlier([Customer]) && Table[Order ID] = earlier( Table[Order ID] )-1 ) , Table[Amount])

return

if(isblank(_last), blank(), Table[Amount]+_last)

[Anonymous · Accepted solution]

Hi @HeirsPowerBi 

For Previous ID column you can use merge queries function

1. Add 2 New Columns with Index Starting from 0 and 1

2. Go to Home Tab > click on Merge Queries>Index left join with Index.1 > Sort Rows

For Plus Previous you can add a custom column .

List.Sum(List.FirstN(#"Renamed Columns"[Amount],[Index.1]))

Hope that helps.

[HeirsPowerBi]

Okay so this works perfectly for the previous order ID but the Solution for plus previous is not working exactly as i would have hoped but my way around it was to get the previous amount in one column and then create another column that adds the current one to the previous one.

Thank You so much