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

[SimonCJ]

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

 

[horseyride]

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"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"

 

[horseyride]

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

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),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Spend as % of last year spend", each [Spend]/[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
    in #"Removed Columns"

 

[pinarello]

Hi,

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

 

[horseyride]

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

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),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Spend as % of last year spend", each [Spend]/[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
 in #"Removed Columns"

 

[pinarello]

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.

 

[Bob Phillips]

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