Refer to row for a new column

[jeanramonyap]

I have a table with the first 4 columns and I would like to create the last column. What is the best way to go about this? 

I have seen people use rank but will this work when I have more and more rows of data? What can I do when I will have a year 2025-01 in my dataset?

ID	Year	Month	Quantity 1	Prior Month Quantity 1
1234	2024	04	100	0
1234	2024	05	250	100
1234	2024	06	300	250
5678	2024	04	-150	0
5678	2024	05	300	-150
5678	2024	06	200	300
2222	2024	04	200	0
2222	2024	05	-150	200
2222	2024	06	100	-150

[v-tangjie-msft]

Hi @jeanramonyap ,

Thanks @BenjaminSNN  for the quick reply and solution. Please allow me to offer other idea:

We can create a column in PBI Desktop.

Prior Month Quantity 1 = 
var _date=DATE([Year],[Month],1)
var _last_date=EOMONTH(_date,-1)
var _qua= CALCULATE(SUM('Table'[Quantity 1]),FILTER('Table',[ID]=EARLIER('Table'[ID]) && [Year]=YEAR(_last_date)&& [Month]=MONTH(_last_date)))
RETURN IF(ISBLANK(_qua),0,_qua)

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BenjaminSNN · Accepted solution]

Hey!

I think this is best done by adding a custom column in Power Query.
Basically you create a unique ID based on ID-Year-Month and a ID for previous month ID-Year-(Month -1) then you can join the query with itself to get values from the previous month within that ID.

Code:

let
Source =YOURSOURCE,
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Quantity 1", Int64.Type}}),
#"+ TempId" = Table.AddColumn(#"Changed Type", "TempID", each Text.From([ID]) & "-" & Text.From([Year]) & "-" & Text.From([Month])),
#"+ TempId_prev" = Table.AddColumn(#"+ TempId", "TempId_prev", each Text.From([ID]) & "-" & Text.From([Year]) & "-" & Text.From([Month] - 1)),
#"Self-join" = Table.NestedJoin(#"+ TempId_prev", {"TempId_prev"}, #"+ TempId_prev", {"TempID"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Self-join", "Added Custom", {"Quantity 1"}, {"Prior Month Quantity 1"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Added Custom",null,0,Replacer.ReplaceValue,{"Prior Month Quantity 1"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"TempID", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"TempID", "TempId_prev"})
in
#"Removed Columns"

[davidding]

yes, agree that generally speaking power query is better, because it can scale up to larger datasets. DAX option may run into performance issues.