You could do this by creating a custom form in PowerApps. When you change the branch it will filter the list based on that value and set the default value for 'Balance':
How i did this
- Start by creating a new form. From the top menu, select "PowerApps" and "Customize Forms"
- Remove any fields you do not want to display. Simply select the section and press delete.
- On your "Start balance" field, click inside the value input, then click "Advanced"
- Press the padlock on top that says "Unlock to change properties"
- Scroll down to the Default value. In here, add the following formula:
First(SortByColumns(Filter('List formatting', Branch.Value = DataCardValue4.Selected.Value),"Modified",Descending)).'End balance'
Adjust with own values. In this example,
List formatting is the list name,
DataCardValue4 is the control from PowerApps that has the branches,
End balance is the calculated field.
This formula filters the existing list on branch and pulls the newest one (or first one, when sorted by modified date descending...)
- I also set the display mode of the field to be
DisplayMode.View so that it can't be edited.
- When happy with your changes, press the File tab, Save & Publish to SharePoint
Note: You probably want some more logic in here - like when the form is in edit mode. Then you probably don't want your formula to run...