Thread: Calculating a % relative to a previous row / Referencing other rows in calculation

1. Calculating a % relative to a previous row / Referencing other rows in calculation

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

2. If source data is already always sorted Year within Customer ID, then merging the table onto itself using two indexes should work

Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Customer ID", "Year", "Spend"}, {"Customer ID.1", "Year.1", "Spend.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index.1", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Customer ID]=[Customer ID.1] and [Year]=[Year.1]+1 then [Spend]/[Spend.1] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Customer ID.1", "Year.1", "Spend.1"})
in
#"Removed Columns"```

3. This would work regardless of sort, but I think the above method would be more efficient for larger data sets if original data came in sorted

Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", (i) => List.Sum(Table.SelectRows(Source, each ([Customer ID] = i[Customer ID] and [Year]+1 = i[Year]))[Spend]) , type number),
in #"Removed Columns"```

4. Hi,

my solution is mor or less equl to #2 of horseyride. the solution of #3 does prodcude unfortunatelly "Error" in the calcvulated column.

5. You have to have Year and Spend as numbers for it to work For your file...

Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,",","",Replacer.ReplaceText,{"Spend"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","£","",Replacer.ReplaceText,{"Spend"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Year", type number}, {"Spend", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (i) => List.Sum(Table.SelectRows(#"Changed Type", each ([Customer ID] = i[Customer ID] and [Year]+1 = i[Year]))[Spend]) , type number),
in #"Removed Columns"```

6. Hello horseyride,

yeah, you're right. That's how it works now. I had a little hope that the addressing method you used was faster than the "normal" access with an index. But unfortunately this was not the case. With 10,000 lines the calculation took about 2 minutes. The method with the join is of course, as expected, very fast. And although I am not the questioner, I learned that I can define the join directly in the query. So far I have always taken the detour "Load as connection" and "Merge". But it's much more elegant that way.

7. Can I ask why you want to use Power Query and not DAX measures for this?