Compare two rows and return return the value

[Anonymous]

Hi All,

I have one probelm with comparing to rows in power.

excel formula is... IF(Part_Number_row2=Part_Number_row1 , Value_row1 , Value_row2)

if part number from row2 and row1 matched thes then want retun Value from row1 else value from row2.

My table look like below.

Year	Month	Part Number	Value
2019	Jan	101	2.914618
2019	Jan	105	1.29267
2019	Feb	105	1.273633
2019	Mar	105	1.258231
2019	Sep	107	5.208029
2019	Oct	107	5.487181
2020	Jan	105	1.254346
2020	Feb	101	2.879371
2020	Feb	105	2.281708
2020	Apr	105	2.248105
2020	Jun	105	5.377196
2020	Sep	107	5.312167
2020	Oct	107	5.316702

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You will need to add an index column.

With the index column you can create a calculated cloumn.

Check the formula below.

Column = 
var column1 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
var column2 = CALCULATE(MAX('Table'[Part Number]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
return
IF('Table'[Part Number]=column2,column1,'Table'[Value])

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

@Anonymous , better you have date column or create one like suggested

date = "1-"&[Month] & "-"&[Year] // Change data type to Date

New column
last date = maxx(filter(table,[date] <earlier([date]) && [Part Number] =earlier([Part Number])),[date])
last value = maxx(filter(table,[date] =earlier([last date ]) && [Part Number] =earlier([Part Number])),[value])

You can use last value and compare value and use

[Anonymous]

I am getting an error if we have same date in two consecutive rows.