Automatically grab previous year value from another row within same Sharepoint List

[Craig1S]

Hi,

What I want to do is, within the same Sharepoint List, have each row show the value from the previous year, if available, if not available then to leave the cell blank.

This is how my data is structured:

kpi_id	year	value	previous year value
measure01	2021	100
measure01	2022	105
measure02	2021	225
measure02	2022	250
measure03	2021	560
measure03	2022	670

This is how I want it to be displayed:

kpi_id	year	value	previous year value
measure01	2021	100
measure01	2022	105	100
measure02	2021	225
measure02	2022	250	225
measure03	2021	560
measure03	2022	670	560

As far as I know, this cannot be done with lookup columns, but it must be something that can be achieved using a workaround - for example, with Power Automate/Flows.

Any help would be much appreciated.

[#2]

@Craig1S yes this can be done with a flow. In my SharePoint list the Year column is a number column.

 Get the last year with a compose using the expression add(outputs('Get_item')?['body/Year'],-1)

Add a filter query to the SharePoint get items usinf Title eq '{Title}' and Year eq '{Outputs of the LastYear compose}'

The get items will only bring back 1 item but you still need the apply to each and for the new row you've just created, update it with value selected from the get items  section of the dynamic content box.

Rob
Los Gallardos
Microsoft Power Automate Community Super User