Updating table rows based on earlier row

Hi all,

I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source.

The solution needs to:

• Find instances where a flag has indicated a manual adjustment has been made to a user entry
• Identify any entries associated with this user ID between the date on which the manual adjustment was made and the date on which the source data has been updated to reflect the manual adjustment (if applicable)
• Apply a specific set of adjustments to the identified rows, dependent on the nature of the adjustment.

Considering the attached table, I’ve tried to outline the logic I’m trying to implement below:

For the purposes of this explanation, I’ll refer to the manual adjustment as row X.

IF Flag = “Adj” (

                IF Case = “L” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Output = “H”

                )

                IF Case = “M” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Level = X.Level

                )

)

Any advice on how to optimally achieve this within PowerBI would be greatly appreciated.

IDCaseFlagLevelDateOutput