Row Count value assigned to Previous Row

[sivasrao]

The below table has 2 columns, 

Event	Target
C	0
C	0
C	3
R	0
R	0
R	0
C	0
C	2
R	0
R	0

My requirement is Target Column.

Target: 

The count of the R value should assign to the previous row where the first R is come appear.

In this example, R will start from the 4th row up to the 6th row. The total Count is 3.

The value 3 should assign to the 3rd row ( previous cell where the first R appears)

Same as the 9th and 10th rows having R, the count is 2. That should assign to the 8th cell.

Thank you.

[v-rzhou-msft]

Hi @sivasrao ,

Firstly, I think you need to add an index column in Power Query Editor.

Then you can create a calculated column by below code to achieve your goal.

Target = 
VAR _Step1 =
    ADDCOLUMNS (
        'Table',
        "Column1",
            CALCULATE (
                MIN ( 'Table'[Index] ),
                FILTER (
                    'Table',
                    'Table'[Index] >= EARLIER ( 'Table'[Index] )
                        && 'Table'[Event] = "R"
                )
            )
    )
VAR _Step2 =
    ADDCOLUMNS (
        _Step1,
        "Column2",
            MAXX (
                FILTER ( _Step1, [Event] <> "R" && [Index] <= EARLIER ( [Index] ) ),
                [Column1]
            )
    )
VAR _Step3 =
    ADDCOLUMNS (
        _Step2,
        "Column3",
            VAR _MAXR =
                MAXX ( FILTER ( _Step2, [Column2] = EARLIER ( [Column2] ) ), [Index] )
            RETURN
                IF ( [Index] = [Column1] - 1, _MAXR - [Column2] + 1, 0 )
    )
RETURN
    SUMX ( FILTER ( _Step3, [Index] = EARLIER ( 'Table'[Index] ) ), [Column3] )

Result is as below.

Best Regards,
Rico Zhou

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

[sivasrao]

Thank you for the reply.

   Perfect solution.👍 

But a little bit confused about DAX.  🙂