I have a PowerPivot data model with spend data by year for 4 years for approximately 1 million customers. I'm looking to use Power Query to transform the data to calculate the % of previous year spend achieved for each customer for each year. I need to do this at a row level because I then want to be able to segment customers based on these %s. E.g. compare customers who spent =>80% of what they spent last year with customers who spent <80% of what they spent last year.

This is how the data is structured. The fourth column is what I'm hoping to add:

Customer ID Year Spend Spend as % of last year spend
1001 2015 2,000 NULL
1001 2016 1,000 50%
1001 2017 1,500 150%
1002 2015 100 NULL
1002 2016 200 200%
1002 2017 150 75%
1003 2015 10,000 NULL
1003 2016 1,000 10%
1003 2017 12,000 120%


Thank you in advance for any help and guidance!

Simon