cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Get Sum of Current and Previous for each Customer

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

3 REPLIES 3
Super User

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
Not applicable

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.

Helper I

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors