Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
HeirsPowerBi
Helper I
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.

 

CustomerOrder IDAmountDate
123500001/09/2022
124600002/09/2022
125700003/09/2022
126300004/09/2022
227390001/09/2022
228400002/09/2022
229290003/09/2022
230100004/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    23500001/09/2022  
1    24600002/09/2022      2311000
1    25700003/09/2022      2413000
1    26300004/09/2022      2510000
2    27390001/09/2022  
2   28400002/09/2022     277900
2   29290003/09/2022     286900
2   30100004/09/2022     293900

 

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

Dax_Noob_0-1662022911770.png

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

Dax_Noob_1-1662022956684.png

 


For Plus Previous you can add a custom column .

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


Hope that helps.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

Dax_Noob_0-1662022911770.png

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

Dax_Noob_1-1662022956684.png

 


For Plus Previous you can add a custom column .

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


Hope that helps.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.