Updating table rows based on earlier row

[PaulBoden]

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

1	HC	Source	1	03/07/2024	S
1	HC	Source	1	04/07/2024	S
1	L	Adj	1	05/07/2024	S
1	HC	Source	1	06/07/2024	S
1	HC	Source	1	07/07/2024	S
1	L	Source	1	08/07/2024	S
1	HC	Source	1	09/07/2024	S
1	HC	Source	1	10/07/2024	S
1	HC	Source	1	11/07/2024	S
1	HC	Source	1	12/07/2024	S
2	HC	Source	3	03/07/2024	S
2	HC	Source	3	04/07/2024	S
2	HC	Source	3	05/07/2024	S
2	HC	Source	3	06/07/2024	S
2	HC	Source	3	07/07/2024	S
2	L	Source	3	08/07/2024	S
3	HC	Source	3	03/07/2024	S
3	HC	Source	3	04/07/2024	S
3	HC	Source	3	05/07/2024	S
3	HC	Source	3	06/07/2024	S
3	HC	Source	3	07/07/2024	S
3	L	Adj	3	08/07/2024	S
3	HC	Source	3	09/07/2024	S
3	HC	Source	3	10/07/2024	S
3	HC	Source	3	11/07/2024	S
3	HC	Source	3	12/07/2024	S
4	HC	Source	5	03/07/2024	S
4	HC	Source	5	04/07/2024	S
4	L	Adj	5	05/07/2024	S
4	HC	Source	5	05/07/2024	S
4	HC	Source	5	06/07/2024	S
4	HC	Source	5	07/07/2024	S
4	HC	Source	5	08/07/2024	S
4	HC	Source	5	09/07/2024	S
4	HC	Source	5	10/07/2024	S
4	HC	Source	5	11/07/2024	S
4	L	Source	5	12/07/2024	S
5	HC	Source	2	03/07/2024	S
5	M	Adj	3	04/07/2024	S
5	HC	Source	2	04/07/2024	S
5	HC	Source	2	05/07/2024	S
5	HC	Source	2	06/07/2024	S
5	HC	Source	2	07/07/2024	S
5	HC	Source	2	08/07/2024	S
5	M	Source	3	09/07/2024	S
5	HC	Source	3	10/07/2024	S
5	HC	Source	3	11/07/2024	S
5	HC	Source	3	12/07/2024	S
5	HC	Source	3	13/07/2024	S

[dufoq3]

Hi @PaulBoden, could you provide expected result based on sample data pls?

[Sahir_Maharaj · Accepted solution]

Hello @PaulBoden,

Can you please try the following:

1. Identify Adjustment Rows

IsAdjustment = 
IF (
    Data[Flag] = "Adj",
    TRUE(),
    FALSE()
)

2. Apply the logic for updating the Output column

AdjustedOutput = 
VAR CurrentID = Data[ID]
VAR CurrentDate = Data[Date]
VAR CurrentCase = Data[Case]
VAR AdjustmentDate = 
    CALCULATE (
        MIN(Data[Date]),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] <= CurrentDate
        )
    )
VAR AdjustmentCase = 
    CALCULATE (
        FIRSTNONBLANK(Data[Case], 1),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] = AdjustmentDate
        )
    )
VAR AdjustmentLevel = 
    CALCULATE (
        FIRSTNONBLANK(Data[Level], 1),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] = AdjustmentDate
        )
    )
RETURN 
    IF (
        Data[Flag] <> "Adj" && Data[Date] >= AdjustmentDate,
        IF (
            AdjustmentCase = "L",
            "H",
            IF (
                AdjustmentCase = "M",
                AdjustmentLevel,
                Data[Output]
            )
        ),
        Data[Output]
    )

Let me know if you might require any further assistance.